﻿
Option Explicit On

'Imports System.DirectoryServices

Imports System.Text
'Imports Acrobat
Imports System.IO
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Word


Imports System.Security
Imports System.Security.Principal
Imports System.Threading
Imports System.Globalization
Imports System.Windows.Forms
'Imports System.Text.StringBuilder
Imports CRM.BusinessLayer
'Imports CRM.BusinessLayer.UploadFiletoDocStore

Public Class Letters
    Public Enum VirtualDir
        Company = 1
        Files = 2
    End Enum
    Public Enum LetterTypes
        PDF = 2
    End Enum
    Public Structure EmailTemplate
        Public Subject As String
        Public Body As String
    End Structure
    Public Enum OutputTypes
        Print = 1
        Email = 2
        File = 3
    End Enum

    Public Structure BookmarkInfo
        Public Bookmark_ID As Integer
        Public Bookmark_SQL As String
        Public Bookmark_SQLName As String
        Public BookMark_DataLevelID As Integer
        Public Bookmark_DataType As Integer
        Public Bookmark_Legacy As Integer
    End Structure

    Public Structure DataSourceInfo
        Public TableDef As String
        Public IDColumn As String
        Public Type As Integer
    End Structure

    Public Structure LetterInfo
        Public LetterId As Integer
        Public LetterName As String
        Public Filepath As String
        Public CustomerType As Integer
        Public OutputType As Integer
        Public LetterType As Integer
        Public Filetype As Integer
    End Structure

    Public Structure LetterPackInfo
        Public LetterPackId As Integer
        Public LetterPackName As String
        Public TypeId As Integer
    End Structure

    Public Structure SLSLinkInfo
        Public LinkId As Integer
        Public SLSId As Integer
        Public LetterpackId As Integer
        Public CustomerType As Integer
        Public StageStart As Integer
        Public StageEnd As Integer
        Public PrintOrder As Integer
        Public ChildSLSId As Integer
    End Structure

    Public Structure SLSInfo
        Public SLSId As Integer
        Public SLSName As String
        Public SLSTypeId As Integer
    End Structure


    Private Shared _LettersInPack As Boolean = False
    Private Const PDSaveFull = 1
    'Private Const wdDoNotSaveChanges = 0
    Public Gbl_Str_CodePath As String

    Public Shared Function StockLetterUsage(ByVal LetterPackId As Integer) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Try
            Return db.GetSQLScalar("Select Count(SLSLink_ID) as SLSCount FROM I_SL_SLSLink WHERE SLSLink_LetterPackID = " & LetterPackId)

        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try

    End Function

    Public Shared Function LetterLinkPrintOrder(ByVal LinkId As Integer) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Try
            Return db.GetSQLScalar("Select Letterlink_printorder from D_Letterlink where Letterlink_ID = " & LinkId)
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try

    End Function

    Public Shared Function LetterUsage(ByVal LetterId As Integer) As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_LetterLink.LetterLink_LetterPackID, D_LetterPacks.LetterPack_Name, ")
                .Append(" Case D_LetterLink.LetterLink_CustomerType when 1 then 'Private' when 2 then 'Corporate' else 'All' End as CType, ")
                .Append(" L_OutputTypes.Output_Name , D_LetterLink.LetterLink_Copies")

                .Append(" FROM D_LetterLink INNER JOIN")
                .Append(" L_OutputTypes ON D_LetterLink.LetterLink_OutputType = L_OutputTypes.Output_ID INNER JOIN")
                .Append(" D_LetterPacks ON D_LetterLink.LetterLink_LetterPackID = D_LetterPacks.LetterPack_ID")

                .Append(" WHERE D_LetterLink.LetterLink_LetterID = " & LetterId)
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            Sql = Nothing
        End Try
    End Function

    Private Shared Function GetPDFFields(ByVal LetterId As Integer, ByVal PDFFieldName As String) As DataTable

        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Select PDFField_ID ")
                .Append(" , PDF_Fields.PDFField_Coor0, PDF_fields.PDFField_Coor1 ")
                .Append(" , PDF_fields.PDFField_Coor2, PDF_fields.PDFField_Coor3")
                .Append(" , PDF_fields.PDFField_Page")
                .Append(" From PDF_fields ")
                .Append(" Where PDFField_PDFID = " & LetterId)
                .Append(" And PDFfield_Name = '" & PDFFieldName & "'")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ScanPDF(ByVal LetterId As Integer) As Integer

        MsgBox("PDF's not working yet")
        'Dim ScanType As Integer
        'Dim formApp As AFORMAUTLib.AFormApp
        'Dim AcroForm As AFORMAUTLib.Fields
        'Dim avDoc As CAcroAVDoc
        'Dim AcroField As AFORMAUTLib.Field
        'Dim IntFieldCount As Integer, IntLoop As Integer
        'Dim StrList As String, StrFilepath As String, StrDocName As String
        'Dim myLetterInfo As Letters.LetterInfo
        'Dim myData As DataTable
        'Dim PDFFieldName As String
        'Dim myJavaScript As String
        'Dim SQL As StringBuilder
        'Dim Msg As New StringBuilder

        'avDoc = CreateObject("AcroExch.AVDoc")
        'formApp = CreateObject("AFormAut.App")

        ''First off see if this form has already been scanned.

        'If DB.GetSQLScalar("Select count(*) from PDF_Fields where PDFField_PDFID = " & LetterId) = 0 Then
        '    'No registered fields, just add what we find
        '    ScanType = 0
        'Else
        '    'Fields already present. Need to compare scanned fields with what is registered and apply changes.
        '    ScanType = 1
        'End If

        ''Get file details.

        'myLetterInfo = Letters.GetLetter(LetterId)

        'If myLetterInfo.LetterId = 0 Then
        '    MsgBox("File not found", MsgBoxStyle.Exclamation, "Error")
        '    Exit Function
        'End If

        'If myLetterInfo.Filetype <> 2 Then
        '    MsgBox("Not a PDF", MsgBoxStyle.Exclamation, "Error")
        '    Exit Function
        'End If

        ''StrFilepath = Rst.Fields("Letter_FilePath")
        ''StrDocName = Rst.Fields("Letter_Name")


        'avDoc.Open(myLetterInfo.Filepath, myLetterInfo.Filepath)

        'AcroForm = formApp.Fields

        'For Each AcroField In AcroForm
        '    IntFieldCount = IntFieldCount + 1

        '    PDFFieldName = AcroField.Name


        '    If ScanType = 1 Then
        '        'See if this field is already there.

        '        myData = Letters.GetPDFFields(LetterId, PDFFieldName)

        '        If myData.Rows.Count = 0 Then
        '            'Not there, add it in
        '            IntAdd = 1
        '        Else
        '            'aha, found it!
        '            IntAdd = 0

        '            'Compare Coordinate and page values and update if necesary
        '            IntLoop = 0
        '            SQL = New StringBuilder

        '            Do

        '                myJavaScript = "event.value = this.getField(""" & PDFFieldName & """).rect[" & IntLoop & "];"
        '                If myData.Columns("PDFField_Coor" & IntLoop) <> AcroForm.ExecuteThisJavascript(myJavaScript) Then

        '                    If SQL.ToString <> "" Then SQL.Append(", ")

        '                    SQL.Append(" PDFField_Coor" & IntLoop & " = " & AcroForm.ExecuteThisJavascript(myJavaScript))
        '                    Msg.Append("Field No. " & myData.Columns("PDFField_ID") & " (" & PDFFieldName & "): Coor" & IntLoop & " updated from " & Rst.Fields("PDFField_Coor" & IntLoop) & "to " & AcroForm.ExecuteThisJavascript(myJavaScript))

        '                End If

        '                IntLoop = IntLoop + 1

        '            Loop While IntLoop < 4

        '            myJavaScript = "event.value = this.getField(""" & PDFFieldName & """).page;"
        '            If myData.Columns("PDFField_Page") <> AcroForm.ExecuteThisJavascript(myJavaScript) Then

        '                If strSQL <> "" Then strSQL = strSQL & ", "

        '                strSQL = strSQL & " PDFField_Page = " & AcroForm.ExecuteThisJavascript(StrScript)
        '                Msg.Append("Field No. " & Rst.Fields("PDFField_ID") & " (" & StrName & "): Page No. updated from " & Rst.Fields("PDFField_Page") & "to " & AcroForm.ExecuteThisJavascript(StrScript))

        '            End If

        '            If strSQL <> "" Then
        '                strSQL = "Update PDF_Fields Set " & strSQL
        '                strSQL = strSQL & " Where PDFField_ID = " & Rst.Fields("PDFfield_ID")
        '                Rst.Close()

        '                If GSI(strSQL) = 0 Then Exit Function
        '            End If
        '        End If
        '        Rst.Close()

        '    Else
        '        'New scan, add the field
        '        IntAdd = 1
        '    End If

        '    Dim StrTemp As String, StrAudit As String

        '    If IntAdd = 1 And AcroField.Type = "Text" Then


        '        StrMess = StrMess & "Field Registration added: " & StrName & vbCrLf

        '        IntLoop = 0
        '        StrTemp = string.empty
        '        StrAudit = string.empty


        '        strSQL = "Insert Into PDF_Fields ( PDFField_PDFID, PDFField_FieldName, PDFField_Coor0, PDFField_Coor1, PDFField_Coor2, PDFField_Coor3, PDFField_Page) "
        '        strSQL = strSQL & "VALUES(" & LID & ", '" & RC(StrName) & "',"

        '        StrAudit = StrName & " : "

        '        StrScript = "event.value = this.getField(""" & StrName & """).rect[0];"
        '        StrTemp = AcroForm.ExecuteThisJavascript(StrScript) & ", "
        '        strSQL = strSQL & StrTemp
        '        StrAudit = StrAudit & StrTemp

        '        StrScript = "event.value = this.getField(""" & StrName & """).rect[1];"
        '        StrTemp = AcroForm.ExecuteThisJavascript(StrScript) & ", "
        '        strSQL = strSQL & StrTemp
        '        StrAudit = StrAudit & StrTemp

        '        StrScript = "event.value = this.getField(""" & StrName & """).rect[2];"
        '        StrTemp = AcroForm.ExecuteThisJavascript(StrScript) & ", "
        '        strSQL = strSQL & StrTemp
        '        StrAudit = StrAudit & StrTemp

        '        StrScript = "event.value = this.getField(""" & StrName & """).rect[3];"
        '        StrTemp = AcroForm.ExecuteThisJavascript(StrScript) & ", "
        '        strSQL = strSQL & StrTemp
        '        StrAudit = StrAudit & StrTemp

        '        StrScript = "event.value = this.getField(""" & StrName & """).page;"
        '        StrTemp = AcroForm.ExecuteThisJavascript(StrScript)

        '        If Nz(StrTemp) = string.empty Then StrTemp = "0"

        '        StrTemp = StrTemp & ") "

        '        strSQL = strSQL & StrTemp
        '        StrAudit = StrAudit & StrTemp

        '        StrTemp = Gbl_Str_CodePath & vbCrLf & StrAudit
        '        Gbl_Str_CodePath = Gbl_Str_CodePath & vbCrLf & StrAudit

        '        If GSI(strSQL) = 0 Then Exit Function
        '        Gbl_Str_CodePath = StrTemp


        '    End If

        'Next AcroField

        'If ScanType = 1 Then
        '    'Compare the recorded fields to whats in the document and remove any that are now absent

        '    strSQL = "SELECT PDF_Fields.PDFField_ID, PDF_Fields.PDFField_FieldName, I_SL_Bookmarks.Bookmark_Text"
        '    strSQL = strSQL & " FROM PDF_Fields LEFT OUTER JOIN"
        '    strSQL = strSQL & " I_SL_Bookmarks ON PDF_Fields.PDFField_BookMarkID = I_SL_Bookmarks.Bookmark_ID"
        '    strSQL = strSQL & " WHERE (PDF_Fields.PDFField_PDFID = " & LID & ")"

        '    Rst = GSS(strSQL)

        '    'No records found, there cant be any fields in the form
        '    Rst.Close()


        'End If

        'avDoc.Close(False)
        'avDoc = Nothing
        'formApp = Nothing
        'AcroForm = Nothing

        'Return IntFieldCount


    End Function

    Public Shared Function LetterAlreadyRegistered(ByVal LetterId As Integer, ByVal FilePath As String) As Boolean

        'Check if this letter has already been registered.

        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            Dim myData As DataTable

            With SQL
                .Append("Select Letter_ID, Letter_Name FROM D_Letters")
                .Append(" WHERE Letter_Filepath = '" & cmn.SwapQuotes(FilePath) & "'")
                .Append(" AND Letter_ID <> " & LetterId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If myData.Rows.Count > 0 Then
                'Letter already registered
                Dim Msg As New StringBuilder

                With Msg
                    .Append("That template is already registered as letter number " & myData.Rows(0).Item("Letter_ID") & " (" & myData.Rows(0).Item("Letter_Name") & ")")
                    .Append(vbCrLf & "You cannot register the same template more than once, please use the other registration entry.")
                End With
                myData.Dispose()

                MsgBox(Msg.ToString, MsgBoxStyle.Exclamation, "Error")
                Return True

            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function LetterNameInUse(ByVal LetterId As Integer, ByVal LetterName As String) As Boolean

        'Check if letter name has already been used.

        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable

            With SQL
                .Append("Select Letter_ID, Letter_Name FROM D_Letters")
                .Append(" WHERE Letter_name = '" & cmn.SwapQuotes(LetterName) & "'")
                .Append(" AND Letter_ID <> " & LetterId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If myData.Rows.Count > 0 Then
                'Letter already registered
                Dim Msg As New StringBuilder

                With Msg
                    .Append("There is already a letter registration with that name, number " & myData.Rows(0).Item("Letter_ID") & " (" & myData.Rows(0).Item("Letter_Name") & ")")
                    .Append(vbCrLf & "To avoid confusion, please enter a different name.")
                End With

                myData.Dispose()

                MsgBox(Msg.ToString, MsgBoxStyle.Exclamation, "Error")
                Return True
                Exit Function
            End If


        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function DeleteLetterFromPack(ByVal LinkId As Long, ByVal LetterId As Integer, ByVal LetterName As String, ByVal LetterPackId As Integer, ByVal LetterPackName As String) As Boolean
        Dim db As New CRM.DataLayer.DataAccess
        Dim tsk As New TasksEvents

        Try

            If db.ExecuteSQL("Delete from D_LetterLink where LetterLink_ID = " & LinkId) Then
                Dim Msg As String

                Msg = "Letter number " & LetterId & " (" & LetterName & ") removed from Letterpack number " & LetterPackId & "(" & LetterPackName & ")"

                tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function DeleteLetterPack(ByVal LetterPackId As Integer, ByVal LetterPackName As String, ByVal Usage As Integer) As Boolean
        Dim db As New CRM.DataLayer.DataAccess
        Dim tsk As New TasksEvents

        Try
            If Usage > 0 Then
                If Not db.ExecuteSQL("Delete from I_SL_SLSLink where SLSLInk_LetterpackID = " & LetterPackId) Then
                    Exit Function
                End If
            End If

            If Not db.ExecuteSQL("Delete from D_Letterpacks where Letterpack_ID = " & LetterPackId) Then
                Exit Function
            End If

            Dim Msg As String

            Msg = "Letterpack no. " & LetterPackId & " (" & LetterPackName & ") Deleted"

            tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

            Return True
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GetLetterPack(ByVal LetterPackId As Integer) As LetterPackInfo
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myTab As DataTable
            Dim myInfo As New LetterPackInfo

            With SQL
                .Append("select letterpack_id,letterpack_name,letterpack_lettertypeid from d_letterpacks")
            End With

            myTab = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myTab) Then

                If myTab.Rows.Count > 0 Then
                    With myInfo
                        .LetterPackId = myTab.Rows(0).Item("letterpack_id")
                        .LetterPackName = myTab.Rows(0).Item("letterpack_name")
                        .TypeId = myTab.Rows(0).Item("letterpack_lettertypeid")
                    End With
                End If

            End If

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    Public Shared Function GetStockLetterSet(ByVal SLSId As Integer) As SLSInfo

        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            Dim myTab As DataTable
            Dim myInfo As New SLSInfo

            SQL = New StringBuilder
            With SQL
                .Append("select sls_id,sls_name,sls_typeid from I_SL_StockLetterSets ")
                .Append("where sls_id = " & SLSId)
            End With

            myTab = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myTab) Then

                If myTab.Rows.Count > 0 Then
                    With myInfo
                        .SLSId = myTab.Rows(0).Item("sls_id")
                        .SLSName = IIf(IsDBNull(myTab.Rows(0).Item("sls_name")), "", IsDBNull(myTab.Rows(0).Item("sls_name")))
                        .SLSTypeId = myTab.Rows(0).Item("sls_typeid")
                    End With
                End If
            End If

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    Public Shared Function GetSLSLink(ByVal LinkId As Integer) As SLSLinkInfo
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myTab As DataTable
            Dim myInfo As New SLSLinkInfo

            With SQL
                .Append("select slslink_id,slslink_slsid,slslink_letterpackid,slslink_customertype,slslink_stagestart,slslink_stageend,slslink_order,slslink_childslsid ")
                .Append("from I_SL_SLSLink where slslink_id = " & LinkId)
            End With

            myTab = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myTab) Then
                If myTab.Rows.Count > 0 Then
                    With myInfo
                        .LinkId = myTab.Rows(0).Item("slslink_id")
                        .SLSId = myTab.Rows(0).Item("slslink_slsid")
                        .LetterpackId = myTab.Rows(0).Item("slslink_letterpackid")
                        .CustomerType = myTab.Rows(0).Item("slslink_customertype")
                        .StageStart = myTab.Rows(0).Item("slslink_stagestart")
                        .StageEnd = myTab.Rows(0).Item("slslink_stageend")
                        .PrintOrder = myTab.Rows(0).Item("slslink_order")
                        .ChildSLSId = myTab.Rows(0).Item("slslink_childslsid")
                    End With
                End If

            End If

            myTab.Dispose()

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    Public Shared Function GetLetter(ByVal LetterId As Integer) As LetterInfo
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            Dim myTable As DataTable
            Dim myLetter As New LetterInfo

            With SQL
                .Append("select letter_id,letter_name,letter_filepath,letter_customertype,letter_outputtype,letter_type,letter_filetype")
                .Append(" from d_letters where letter_id = " & LetterId)
            End With

            myTable = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myTable) Then

                If myTable.Rows.Count > 0 Then

                    Dim myRow As DataRow = myTable.Rows(0)

                    With myLetter
                        .LetterId = myRow.Item("letter_id")
                        .LetterName = IIf(IsDBNull(myRow.Item("letter_name")), "", myRow.Item("letter_name"))
                        .Filepath = IIf(IsDBNull(myRow.Item("letter_filepath")), "", myRow.Item("letter_filepath"))
                        .CustomerType = myRow.Item("letter_customertype")
                        .OutputType = myRow.Item("letter_outputtype")
                        .LetterType = myRow.Item("letter_type")
                        .Filetype = myRow.Item("letter_filetype")
                    End With
                End If

            End If


            Return myLetter
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    'Public Shared Function GblFunc_Process_Letterpack(ByVal LetterPackId As Integer, ByVal DataID As Long, ByVal DataType As Integer, ByVal OutputType As OutputTypes, ByVal CustomerType As Integer) As Boolean
    '    Dim db As New CRM.DataLayer.DataAccess
    '    Dim msgDownload As String
    '    'Acrobat declarations
    '    Dim AcroFormApp As AFORMAUTLib.AFormApp, AcroForm As AFORMAUTLib.Fields = Nothing, AcroField As AFORMAUTLib.Field
    '    'Dim a As AcroAVDoc

    '    Dim ArrAcroApp As CAcroApp 'xxx I added this to try and get it working
    '    Dim ArrAcroAVDoc(0 To 20) As CAcroAVDoc
    '    Dim ArrAcroPDDoc(0 To 20) As CAcroPDDoc
    '    Dim WordApp As Word.Application
    '    Dim fileloc As String
    '    Try
    '        'Word Automation declarations
    '        WordApp = New Word.Application
    '        Dim ArrWordDoc() As Word.Document
    '        Dim ObjBookmark As Word.Bookmark
    '        Dim ObjRange As Word.Range
    '        'Dim StrNewFileName As String
    '        'Dim CollBookmarks As Collection
    '        Dim IntWordActive As Integer

    '        'DB declarations

    '        'Arrays to store the document details in
    '        Dim ArrStrDocuments(,) As String, ArrIntCopies() As Integer

    '        'position in and size of array
    '        Dim IntDocArrPos As Integer, IntDocArrSize As Integer

    '        'Details for DocStore

    '        'Dim StrLPName As String, 
    '        Dim LngDocStore As Long, StrFilename As String, StrOType As String = String.Empty, StrExt As String, StrPath As String = String.Empty
    '        Dim StrBMData As String, IntBookmarkNo As Integer

    '        'App handling variables
    '        Dim IntWord As Integer, intPDF As Integer
    '        Dim SQL As New StringBuilder
    '        Dim SQLBookmarks As StringBuilder
    '        Dim LetterPackName As String
    '        Dim myData As DataTable
    '        Dim myRow As DataRow
    '        Dim PrintPDF As Boolean


    '        'Check that Letterpack exists and get it's name
    '        SQL.Append("SELECT D_LetterPacks.LetterPack_Name FROM D_LetterPacks WHERE D_LetterPacks.LetterPack_ID = " & LetterPackId)

    '        LetterPackName = db.GetSQLScalar(SQL.ToString)

    '        If IsNothing(LetterPackName) Then
    '            MsgBox("This letterpack (" & LetterPackId & ") has not been configured, please speak to an administrator", MsgBoxStyle.Exclamation)
    '            Exit Function
    '        End If

    '        'Process Output type
    '        Select Case OutputType
    '            Case OutputTypes.Print
    '                'Printing
    '                StrOType = "(0,1,4,5)"
    '            Case OutputTypes.Email
    '                'EMail
    '                StrOType = "(0,2,4,6)"
    '            Case OutputTypes.File
    '                'File
    '                StrOType = "(0,3,5,6)"
    '        End Select

    '        'Read which documents to print.
    '        SQL = New StringBuilder
    '        With SQL
    '            .Append("SELECT D_Letters.Letter_ID, D_Letters.Letter_FilePath, D_LetterLink.LetterLink_Copies,D_Letters.Letter_Name, D_Letters.Letter_Filetype")
    '            .Append(" FROM D_LetterLink INNER JOIN D_Letters ON D_LetterLink.LetterLink_LetterID = D_Letters.Letter_ID")
    '            .Append(" WHERE D_LetterLink.LetterLink_LetterPackID = " & LetterPackId)
    '            .Append(" AND D_LetterLink.LetterLink_CustomerType in( " & CustomerType & ",0)")

    '            'New code ITD 15-12-04.
    '            'Changed Output Type code to allow dual types on letters, un-comment this line to reverse.
    '            'StrSQL = StrSQL & " AND D_LetterLink.LetterLink_OutputType in (" & OutputType & ",0)"

    '            'Comment this line to reverse
    '            .Append(" AND D_LetterLink.LetterLink_OutputType in " & StrOType)

    '            '/New code
    '            .Append(" ORDER BY D_LetterLink.LetterLink_PrintOrder")
    '        End With

    '        myData = db.GetSQLDataTable(SQL.ToString)

    '        If myData.Rows.Count = 0 Then
    '            MsgBox("No documents defined for this letterpack (with this customer and output types), please speak to your departments stock letter administrator", MsgBoxStyle.Exclamation)
    '            Exit Function
    '        End If

    '        For Each myRow In myData.Rows
    '            If CType(myRow.Item("Letter_Filetype"), LetterTypes) = LetterTypes.PDF Then
    '                PrintPDF = True
    '                Exit For
    '            End If
    '        Next

    '        If PrintPDF Then
    '            'This letterpack contains a PDF file, check to see whether this PC can output them
    '            Select Case GlobalCRM.Gbl_Int_Acrobat
    '                Case 0
    '                    'No Acrobat installed, remove letters from pack and warn the user
    '                    MsgBox("This Letterpack contains PDF files, but this PC does not have any version of Acrobat installed. Pack will be processed without the PDF's", MsgBoxStyle.Exclamation)
    '                    SQL = New StringBuilder
    '                    With SQL
    '                        .Append("SELECT D_Letters.Letter_ID, D_Letters.Letter_FilePath, D_LetterLink.LetterLink_Copies,D_Letters.Letter_Name, D_Letters.Letter_Filetype")
    '                        .Append(" FROM D_LetterLink INNER JOIN D_Letters ON D_LetterLink.LetterLink_LetterID = D_Letters.Letter_ID")
    '                        .Append(" WHERE D_LetterLink.LetterLink_LetterPackID = " & LetterPackId)
    '                        .Append(" AND D_LetterLink.LetterLink_CustomerType in( " & CustomerType & ",0)")
    '                        .Append(" AND D_LetterLink.LetterLink_OutputType in " & StrOType)
    '                        .Append(" AND D_Letters.Letter_FileType = 1 ")
    '                        .Append(" ORDER BY D_LetterLink.LetterLink_PrintOrder")
    '                    End With
    '                    myData = db.GetSQLDataTable(SQL.ToString)
    '                Case 1
    '                    'Acrobat reader, TBC
    '                    If Not OutputType = OutputTypes.Email Then 'email is fine others wont work with acro reader
    '                        MsgBox("This Letterpack contains PDF files, but this PC does not have any version of Acrobat installed. This pack will be processed without the PDF's", MsgBoxStyle.Exclamation)
    '                        SQL = New StringBuilder
    '                        With SQL
    '                            .Append("SELECT D_Letters.Letter_ID, D_Letters.Letter_FilePath, D_LetterLink.LetterLink_Copies,D_Letters.Letter_Name, D_Letters.Letter_Filetype")
    '                            .Append(" FROM D_LetterLink INNER JOIN D_Letters ON D_LetterLink.LetterLink_LetterID = D_Letters.Letter_ID")
    '                            .Append(" WHERE D_LetterLink.LetterLink_LetterPackID = " & LetterPackId)
    '                            .Append(" AND D_LetterLink.LetterLink_CustomerType in( " & CustomerType & ",0)")
    '                            .Append(" AND D_LetterLink.LetterLink_OutputType in " & StrOType)
    '                            .Append(" AND D_Letters.Letter_FileType = 1 ")
    '                            .Append(" ORDER BY D_LetterLink.LetterLink_PrintOrder")
    '                        End With

    '                        myData = db.GetSQLDataTable(SQL.ToString)

    '                        'check if only one file in LP
    '                        If myData.Rows.Count = 0 Then
    '                            MsgBox("No other documents defined for this letterpack , so no files have been processed", MsgBoxStyle.Exclamation)
    '                            Exit Function
    '                        End If

    '                    End If

    '                Case 2
    '                    'Acrobat Standard/Pro process as requested
    '            End Select
    '        End If

    '        'CommonFunc.DebugAssert(1 = 1, "A")

    '        IntDocArrSize = myData.Rows.Count

    '        'Set arrat to required size
    '        ReDim ArrStrDocuments(IntDocArrSize, 5)
    '        ReDim ArrIntCopies(IntDocArrSize)

    '        If IntDocArrSize > 20 Then IntDocArrSize = 20

    '        IntDocArrPos = 0
    '        IntWord = 0
    '        intPDF = 0

    '        For Each myRow In myData.Rows

    '            IntDocArrPos = IntDocArrPos + 1

    '            Select Case myRow.Item("Letter_Filetype")

    '                Case 2
    '                    'Pdf
    '                    StrExt = ".PDF"
    '                    PrintPDF = True
    '                    'intPDF = 1
    '                Case Else
    '                    'Other, treat as Word
    '                    StrExt = ".Doc"
    '                    IntWord = 1
    '            End Select

    '            'CommonFunc.DebugAssert(1 = 1, myRow.Item("Letter_Name"))

    '            ArrStrDocuments(IntDocArrPos, 1) = (myRow.Item("letter_filepath"))
    '            ArrStrDocuments(IntDocArrPos, 2) = myRow.Item("Letter_name") & "_" & CStr(DataID) & StrExt
    '            ArrStrDocuments(IntDocArrPos, 3) = myRow.Item("Letter_name")
    '            ArrStrDocuments(IntDocArrPos, 4) = myRow.Item("Letter_FileType")
    '            ArrStrDocuments(IntDocArrPos, 5) = myRow.Item("Letter_id")
    '            ArrIntCopies(IntDocArrPos) = myRow.Item("letterlink_Copies")

    '        Next

    '        IntDocArrPos = 0

    '        'Setup Apps and loop through the documents.

    '        If IntWord = 1 Then
    '            'Word needed
    '            If WordApp Is Nothing Then
    '                WordApp = New Word.Application
    '            End If
    '            'CommonFunc.DebugAssert(WordApp Is Nothing, "Word Application Failed to Initialise 1")
    '            ' Write to error log
    '            WordApp.Visible = False
    '            IntWordActive = 1
    '        End If

    '        'If intPDF = 1 Then
    '        'AcroFormApp = CreateObject("AFormAut.App")
    '        'End If

    '        'this loop runs once for each doc in the array

    '        'CommonFunc.DebugAssert(1 = 1, "B")

    '        Do
    '            'Open the document
    '            IntDocArrPos = IntDocArrPos + 1

    '            SQLBookmarks = New StringBuilder

    '            Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                Case 2
    '                    'PDF
    '                    Select Case GlobalCRM.Gbl_Int_Acrobat
    '                        Case 0 'none
    '                        Case 1 'reader
    '                            'may need code for this CH06
    '                        Case 2 'pro/stand

    '                            'xxx I can't get Acrobat to open up and do it's stuff
    '                            ' so i've put in this try statement to handle the error for now

    '                            Try
    '                                ArrAcroPDDoc(IntDocArrPos) = CreateObject("AcroExch.PDDoc")
    '                                Call ArrAcroPDDoc(IntDocArrPos).Open(ArrStrDocuments(IntDocArrPos, 1))
    '                                ArrAcroAVDoc(IntDocArrPos) = ArrAcroPDDoc(IntDocArrPos).OpenAVDoc(ArrStrDocuments(IntDocArrPos, 1))
    '                                AcroFormApp = CreateObject("AFormAut.App")
    '                                'CommonFunc.DebugAssert(AcroFormApp Is Nothing, "AcroformApp is nothing")
    '                                'PDF document has been opened, scan for fields present in document
    '                                'ArrAcroDoc(IntDocArrPos).
    '                                AcroForm = AcroFormApp.Fields
    '                                For Each AcroField In AcroForm
    '                                    If (AcroField.Type = "text") Then
    '                                        'MJG 30/05/06 Quotes in SQL bug fixed here
    '                                        'StrSQLBookmarks = StrSQLBookmarks & "'" & AcroField.Name & "',"
    '                                        SQLBookmarks.Append("'" & Replace(AcroField.Name, "'", "''") & "',")
    '                                    End If
    '                                Next AcroField
    '                            Catch ex As Exception
    '                                MsgBox("Acrobat could not be opened.")
    '                            End Try

    '                    End Select

    '                Case Else
    '                    'Other undefined, treat as Word
    '                    ReDim Preserve ArrWordDoc(IntDocArrPos)

    '                    ArrWordDoc(IntDocArrPos) = New Word.Document
    '                    'CommonFunc.DebugAssert(ArrWordDoc(IntDocArrPos) Is Nothing, "Arrworddoc(" & CStr(IntDocArrPos) & ") is nothing")
    '                    'Write to error log - 



    '                    ' Dim webClient As System.Net.WebClient = New System.Net.WebClient()
    '                    'Dim result As String = webClient.DownloadString(ArrStrDocuments(IntDocArrPos, 1))
    '                    fileloc = CommonFuncs.GetFileName(ArrStrDocuments(IntDocArrPos, 1))
    '                    Dim sSplit() As String = Split(ArrStrDocuments(IntDocArrPos, 1), "\")
    '                    'msgDownload = DownloadFileFromHttpOrFtp(ArrStrDocuments(IntDocArrPos, 1).Replace("F:\", GlobalCRM.VirtualCompanyDir), fileloc, True, GlobalCRM.CurrentNTUserName, GlobalCRM.CurrentNTPassword)
    '                    Dim filePaths As String = String.Empty
    '                    If sSplit.Length > 3 Then
    '                        For i = 2 To sSplit.Length - 2
    '                            'If filePaths = String.Empty Then
    '                            'filePaths = filePaths & sSplit(i)
    '                            'Else
    '                            filePaths = filePaths & "\" & sSplit(i)
    '                            'End If
    '                        Next
    '                    End If
    '                    If DownloadFileWebservice("", sSplit(1), filePaths, fileloc, "J:") = True Then
    '                        'If msgDownload = "True" Then
    '                        If IO.File.Exists("J:\" & sSplit(1) & filePaths & "\" & fileloc) = False Then
    '                            CommonFuncs.DisplayMessage("The file " & fileloc & " does not exist. Please check whether you have j: on your system" & vbCrLf & msgDownload, CommonDialog.CustomDialogIcons.Information, "File")
    '                            Return False
    '                        Else
    '                            ArrWordDoc(IntDocArrPos) = WordApp.Documents.Add("J:\" & sSplit(1) & filePaths & "\" & fileloc)

    '                            'Word document has been opened, scan for bookmarks present in document
    '                            For Each ObjBookmark In ArrWordDoc(IntDocArrPos).Bookmarks
    '                                SQLBookmarks.Append("'" & ObjBookmark.Name & "',")
    '                            Next ObjBookmark
    '                        End If
    '                    Else
    '                        CommonFuncs.DisplayMessage("The File Download was not successful. (The file might not be found on the server or the server is busy or the J: is not configured on your machine)", CommonDialog.CustomDialogIcons.Information, "File download")
    '                        Return False
    '                    End If



    '            End Select

    '            'Did we find any bookmarks at all?
    '            If SQLBookmarks.ToString = String.Empty Then
    '                'No bookmarks found in the document, go onto the next without any data processing.
    '            Else
    '                'Got some bookmarks, now get the datalevels

    '                'this string array can be used in the select clause of queries on the bookmarks table (with PDF this is via the PDF field table)
    '                SQLBookmarks.Remove(SQLBookmarks.Length - 1, 1)

    '                'SQLBookmarks.Append(Left(SQLBookmarks.ToString, (Len(SQLBookmarks.ToString) - 1)))

    '                ' SQLBookmarks.Append("(" & SQLBookmarks.ToString & ")")
    '                SQLBookmarks.Insert(0, "(")
    '                SQLBookmarks.Append(")")

    '                'Select against the Bookmarks table to give a list of recognised bookmarks to be processed.

    '                Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                    Case 2
    '                        'PDF, scan through PDf fields table.
    '                        SQL = New StringBuilder
    '                        With SQL
    '                            .Append("SELECT I_SL_Bookmarks.Bookmark_Text as BMName, PDF_Fields.PDFField_FieldName as FieldName")
    '                            .Append(" FROM PDF_Fields INNER JOIN")
    '                            .Append(" I_SL_Bookmarks ON PDF_Fields.PDFField_BookMarkID = I_SL_Bookmarks.Bookmark_ID")
    '                            .Append(" WHERE (PDF_Fields.PDFField_FieldName in " & SQLBookmarks.ToString & ") AND (PDF_Fields.PDFField_PDFID = " & CInt(ArrStrDocuments(IntDocArrPos, 5)) & ")")
    '                        End With
    '                    Case Else
    '                        'Undefined other, treat as word doc
    '                        SQL = New StringBuilder
    '                        With SQL
    '                            .Append("SELECT BookMark_Text as BMName")
    '                            .Append(" FROM I_SL_Bookmarks")
    '                            .Append(" WHERE Bookmark_Text in " & SQLBookmarks.ToString)
    '                            .Append(" AND Bookmark_VersionID <= " & GlobalCRM.SysVersion)
    '                            .Append(" GROUP BY BookMark_Text")
    '                        End With
    '                End Select

    '                myData = db.GetSQLDataTable(SQL.ToString)
    '                ' CommonFunc.DebugAssert(1 = 1, "BETA")
    '                If myData.Rows.Count > 0 Then
    '                    'Found some bookmarks, loop through and process

    '                    For Each myRow In myData.Rows

    '                        If myRow.Item("BMName") <> "" Then
    '                            'Do we have a bookmark to find?
    '                            StrBMData = Gblfunc_Get_BookmarkData(myRow.Item("BMName"), DataID, DataType)

    '                            If StrBMData <> "" Then
    '                                'Is there any data for that bookmark?

    '                                'Different code for populating the values into the docs based on app type

    '                                Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                                    Case 2
    '                                        'PDF
    '                                        AcroField = AcroForm.Item(myRow.Item("FieldName"))

    '                                        'write the data into the field on the PDF
    '                                        AcroField.Value = StrBMData
    '                                    Case Else
    '                                        'Undefined other, treat as Word Doc
    '                                        If ArrWordDoc(IntDocArrPos).Bookmarks.Exists(myRow.Item("bmname")) Then
    '                                            ObjBookmark = ArrWordDoc(IntDocArrPos).Bookmarks(myRow.Item("bmname"))
    '                                            ObjRange = ObjBookmark.Range
    '                                            'write the data over the bookmark in the word doc
    '                                            ObjRange.Text = StrBMData
    '                                        End If

    '                                        IntBookmarkNo = 1

    '                                        'check for repeat use of the same boomark, keeping looping till they are all dealt with
    '                                        Do While ArrWordDoc(IntDocArrPos).Bookmarks.Exists(myRow.Item("bmname") & IntBookmarkNo)
    '                                            ObjBookmark = ArrWordDoc(IntDocArrPos).Bookmarks(myRow.Item("bmname") & IntBookmarkNo)
    '                                            ObjRange = ObjBookmark.Range
    '                                            ObjRange.Text = StrBMData
    '                                            IntBookmarkNo = IntBookmarkNo + 1
    '                                        Loop

    '                                End Select
    '                            End If
    '                        End If

    '                        'done with this bookmark, onto the next
    '                    Next
    '                    'Done with this document
    '                End If
    '            End If

    '        Loop While IntDocArrPos < IntDocArrSize
    '        'CommonFunc.DebugAssert(1 = 1, "C")
    '        'Documents are now all prepared, time to process them to output

    '        If _LettersInPack Then

    '            'this is a test print so output the documents differently

    '            If 1 = 1 Then 'Form_FMA_LettersInPack.Frm_TestPrintTo = 1 Then
    '                'CommonFunc.DebugAssert(1 = 1, "E")
    '                'Print the Documents
    '                IntDocArrPos = 0

    '                Do
    '                    IntDocArrPos = IntDocArrPos + 1

    '                    Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                        Case 2 'PDF
    '                            Select Case GlobalCRM.Gbl_Int_Acrobat
    '                                Case 0 'none

    '                                Case 1 'reader
    '                                    'need print code CH 06

    '                                Case 2 'PDF pro/stand
    '                                    'Set AcroAVDoc = ArrAcroDoc(IntDocArrPos).OpenAVDoc("printobj")
    '                                    ArrAcroAVDoc(IntDocArrPos).PrintPagesSilent(0, ArrAcroPDDoc(IntDocArrPos).GetNumPages, 2, 1, 1)
    '                                    'AcroAVDoc.Close (1)
    '                                    'Set AcroAVDoc = Nothing

    '                            End Select

    '                        Case Else

    '                            'Undefined other, treat as Word Doc
    '                            ArrWordDoc(IntDocArrPos).PrintOut(Background:=False, Copies:=ArrIntCopies(IntDocArrPos))

    '                    End Select

    '                Loop While IntDocArrPos < IntDocArrSize

    '            Else
    '                'CommonFunc.DebugAssert(1 = 1, "D")
    '                'save the documents to the H: drive
    '                IntDocArrPos = 0

    '                Do
    '                    IntDocArrPos = IntDocArrPos + 1

    '                    Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                        Case 2
    '                            Select Case GlobalCRM.Gbl_Int_Acrobat
    '                                Case 0 'none

    '                                Case 1 'reader
    '                                    'copy file as reader cant save file
    '                                    FileCopy(fileloc, "h:\" & ArrStrDocuments(IntDocArrPos, 2))

    '                                Case 2 'PDF pro/stand
    '                                    Call ArrAcroPDDoc(IntDocArrPos).Save(PDSaveFull, ArrStrDocuments(IntDocArrPos, 2))
    '                            End Select
    '                        Case Else
    '                            'Undefined other, treat as Word Doc
    '                            ArrWordDoc(IntDocArrPos).SaveAs(FileName:=ArrStrDocuments(IntDocArrPos, 2))
    '                    End Select

    '                Loop While IntDocArrPos < IntDocArrSize

    '            End If
    '        Else

    '            'Normal print job, output as instructed
    '            Select Case OutputType
    '                Case 1
    '                    'Print the Documents
    '                    '  CommonFunc.DebugAssert(1 = 1, "F")
    '                    LngDocStore = GblFunc_Create_DocStore(LetterPackName, "Printed Letterpack", DataID, DataType)

    '                    If LngDocStore = 0 Then
    '                        MsgBox("An error has occured in the Document Store, your Letterpack will print but has NOT been saved to the DocStore. Please speak to IT", MsgBoxStyle.Exclamation)
    '                    Else
    '                        StrPath = Gblfunc_Get_DocStorePath(LngDocStore)
    '                    End If

    '                    IntDocArrPos = 0

    '                    Do
    '                        IntDocArrPos = IntDocArrPos + 1

    '                        StrFilename = StrPath & "\" & ArrStrDocuments(IntDocArrPos, 2)

    '                        Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                            Case 2
    '                                'PDF
    '                                Select Case GlobalCRM.Gbl_Int_Acrobat
    '                                    Case 0 'none

    '                                    Case 1 'reader
    '                                        'need print code CH06

    '                                    Case 2 'PDF pro/stand
    '                                        'Set AcroAVDoc = ArrAcroDoc(IntDocArrPos).OpenAVDoc("printobj")
    '                                        ArrAcroAVDoc(IntDocArrPos).PrintPagesSilent(0, ArrAcroPDDoc(IntDocArrPos).GetNumPages, 2, 1, 1)
    '                                        'AcroAVDoc.Close (1)
    '                                        'Set AcroAVDoc = Nothing
    '                                End Select

    '                                If LngDocStore <> 0 Then
    '                                    Call ArrAcroPDDoc(IntDocArrPos).Save(PDSaveFull, StrFilename)
    '                                    Call GblFunc_Add_Doc(StrFilename, ArrStrDocuments(IntDocArrPos, 3), LngDocStore)
    '                                End If

    '                            Case Else
    '                                'Undefined other, treat as Word Doc
    '                                ArrWordDoc(IntDocArrPos).PrintOut(Background:=False, Copies:=ArrIntCopies(IntDocArrPos))
    '                                Try
    '                                    If LngDocStore <> 0 Then
    '                                        ArrWordDoc(IntDocArrPos).SaveAs(FileName:=StrFilename)
    '                                        GblFunc_Add_Doc(StrFilename, ArrStrDocuments(IntDocArrPos, 3), LngDocStore)
    '                                    End If
    '                                Catch ex As Exception

    '                                End Try

    '                        End Select

    '                    Loop While IntDocArrPos < IntDocArrSize

    '                    IntDocArrPos = 0


    '                Case 2
    '                    'CommonFunc.DebugAssert(1 = 1, "G")
    '                    'Save the document to be attached to an e-mail
    '                    IntDocArrPos = 0
    '                    Do
    '                        IntDocArrPos = IntDocArrPos + 1
    '                        StrFilename = "C:\Database\" & ArrStrDocuments(IntDocArrPos, 2)

    '                        Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                            Case 2
    '                                'PDF
    '                                Select Case GlobalCRM.Gbl_Int_Acrobat
    '                                    Case 0 'none
    '                                    Case 1 'reader
    '                                        FileCopy(ArrStrDocuments(IntDocArrPos, 1), StrFilename)
    '                                        'may need code for this CH06
    '                                    Case 2 'pro/stand
    '                                        ' xxx there is a problem here, when emailing the Brazil Tourist visa pack

    '                                        ArrAcroPDDoc(IntDocArrPos).Save(PDSaveFull, StrFilename)
    '                                End Select
    '                            Case Else
    '                                'Undefined other, treat as Word Doc
    '                                ArrWordDoc(IntDocArrPos).SaveAs(FileName:=StrFilename)
    '                        End Select

    '                        'load the local table with the details for later retrieval

    '                        GlobalCRM.LocalAttachments.AddAttachment(ArrStrDocuments(IntDocArrPos, 3), StrFilename)

    '                        'SQL = New StringBuilder
    '                        'With SQL
    '                        '    .Append("Insert into Local_Attachments (Att_Name, Att_filePath, Att_Temp)")
    '                        '    .Append(" VALUES (""" & ArrStrDocuments(IntDocArrPos, 3) & """, """ & StrFilename & """,1)")
    '                        'End With

    '                        'If Not DB.ExecuteSQL(SQL.ToString) Then
    '                        '    Exit Function
    '                        'End If

    '                    Loop While IntDocArrPos < IntDocArrSize

    '                Case 3
    '                    'save the documents to the H: drive for the consultant to further prossess.
    '                    IntDocArrPos = 0

    '                    'CommonFunc.DebugAssert(1 = 1, "H")

    '                    Do
    '                        IntDocArrPos = IntDocArrPos + 1

    '                        Select Case CInt(ArrStrDocuments(IntDocArrPos, 4))
    '                            Case 2
    '                                Select Case GlobalCRM.Gbl_Int_Acrobat
    '                                    Case 0 'none
    '                                    Case 1 'reader
    '                                        FileCopy(fileloc, "H:\" & ArrStrDocuments(IntDocArrPos, 2))
    '                                        'may need code for this CH06
    '                                    Case 2 'pro/stand
    '                                        'PDF pro/stand
    '                                        ArrAcroPDDoc(IntDocArrPos).Save(PDSaveFull, "H:\" & ArrStrDocuments(IntDocArrPos, 2))
    '                                End Select
    '                            Case Else
    '                                'Undefined other, treat as Word Doc

    '                                If Environment.MachineName.ToUpper = "MONSTER" Then
    '                                    ArrWordDoc(IntDocArrPos).SaveAs(FileName:="G:\marting\" & fileloc)
    '                                Else
    '                                    ArrWordDoc(IntDocArrPos).SaveAs(FileName:="H:\" & ArrStrDocuments(IntDocArrPos, 2))
    '                                End If
    '                        End Select


    '                    Loop While IntDocArrPos < IntDocArrSize

    '            End Select

    '        End If

    '        'Documents successfully processed, exit the function

    '        If IntWord = 1 Then

    '            'CommonFunc.DebugAssert(1 = 1, "H")
    '            WordApp.Quit(SaveChanges:=Word.WdSaveOptions.wdDoNotSaveChanges)

    '            IntDocArrPos = 0

    '            Do
    '                IntDocArrPos = IntDocArrPos + 1

    '                If Right(ArrStrDocuments(IntDocArrPos, 2), 3) = "Doc" Then
    '                    ArrWordDoc(IntDocArrPos) = Nothing
    '                End If

    '            Loop While IntDocArrPos < IntDocArrSize

    '            WordApp = Nothing

    '        End If

    '        If PrintPDF Then
    '            IntDocArrPos = 0
    '            Do
    '                IntDocArrPos = IntDocArrPos + 1
    '                ' CommonFunc.DebugAssert(1 = 1, "I")
    '                If GlobalCRM.Gbl_Int_Acrobat = 2 Then
    '                    If Right(ArrStrDocuments(IntDocArrPos, 2), 3) = "PDF" Then
    '                        ArrAcroPDDoc(IntDocArrPos).Close()
    '                        ArrAcroAVDoc(IntDocArrPos).Close(1)
    '                        ArrAcroPDDoc(IntDocArrPos) = Nothing
    '                        ArrAcroAVDoc(IntDocArrPos) = Nothing
    '                    End If
    '                End If

    '            Loop While IntDocArrPos < IntDocArrSize

    '            AcroFormApp = Nothing
    '            AcroForm = Nothing

    '        End If

    '        Return True

    '        Exit Function
    '    Catch ex As Exception
    '        CommonFuncs.ErrorHandler(ex, "letters.gblFunc_Process_Letterpack")
    '    Finally

    '        Try



    '            Dim intLower As Integer = 0
    '            Dim intUpper As Integer = (ArrAcroAVDoc).Length - 1
    '            Dim intCtr As Integer

    '            For intCtr = intLower To intUpper
    '                If Not ArrAcroAVDoc(intCtr) Is Nothing Then
    '                    ArrAcroAVDoc(intCtr).Close(0)
    '                    ArrAcroPDDoc(intCtr).Close()

    '                    ArrAcroAVDoc(intCtr) = Nothing
    '                    ArrAcroPDDoc(intCtr) = Nothing
    '                End If
    '            Next


    '        Catch ex As Exception
    '            ' Do Nothing
    '        End Try

    '        If Not WordApp Is Nothing Then
    '            WordApp.Quit()
    '            WordApp = Nothing
    '        End If

    '        If Not ArrAcroApp Is Nothing Then
    '            ArrAcroApp.CloseAllDocs()
    '            ArrAcroApp.Exit()
    '            ArrAcroApp = Nothing
    '        End If

    '    End Try

    '    'Err_GblFunc_Process_Letterpack:
    '    '        If IntWordActive = 1 Then
    '    '            If IntWord = 1 Then
    '    '                WordApp.Quit(SaveChanges:=Word.WdSaveOptions.wdDoNotSaveChanges)
    '    '                IntDocArrPos = 0
    '    '                Do
    '    '                    IntDocArrPos = IntDocArrPos + 1
    '    '                    ArrWordDoc(IntDocArrPos) = Nothing
    '    '                Loop While IntDocArrPos < IntDocArrSize
    '    '                WordApp = Nothing
    '    '            End If
    '    '            If IntPDF = 1 Then
    '    '                IntDocArrPos = 0
    '    '                Do
    '    '                    IntDocArrPos = IntDocArrPos + 1
    '    '                    ArrAcroPDDoc(IntDocArrPos).Close()
    '    '                    ArrAcroPDDoc(IntDocArrPos) = Nothing
    '    '                    ArrAcroAVDoc(IntDocArrPos).Close(1)
    '    '                    ArrAcroAVDoc(IntDocArrPos) = Nothing
    '    '                Loop While IntDocArrPos < IntDocArrSize
    '    '                AcroFormApp = Nothing
    '    '                AcroForm = Nothing
    '    '            End If
    '    '        End If

    'End Function
    Public Shared Function DownloadFileFromHttpOrFtp(ByVal remotefilepath As String, ByVal filepathandname2savewith As String, ByVal issecure As Boolean, ByVal username As String, ByVal password As String) As String
        Try
            'UploadFileToHttpOrFtp(remotefilepath, filepathandname2savewith, True, username, password)

            Dim client As System.Net.WebClient = New System.Net.WebClient() 'You can have this at class level so dont need to instantiate for each request

            If issecure = True Then

                Dim myCred As System.Net.NetworkCredential = New System.Net.NetworkCredential(username, password, "GV")

                client.Credentials = myCred

            End If

            client.DownloadFile(remotefilepath, filepathandname2savewith)
            Return "True"
        Catch ex As Exception
            Return ex.Message.ToString & vbCrLf & ex.StackTrace.ToString

        End Try
    End Function
    Public Shared Function UploadFileWebservice(ByVal Docstoreid As Long, ByVal filename As String, ByVal FilenameWithPath As String) As Boolean
        Dim Datafile As Byte()
        Dim uploadFileResult As Boolean
        Try

            Datafile = Letters.StreamFileForUpload(FilenameWithPath)
            'Dim gvwebserv As New UploadFiletoDocStore.gvwebservSoapClient("EPName")

            'uploadFileResult = gvwebserv.UploadFile(Docstoreid, filename, Datafile)

            Return uploadFileResult
        Catch ex As Exception
            Return False
        Finally
            Datafile = Nothing
        End Try
    End Function

    Public Shared Function DownloadFileWebservice(ByVal VDirName As VirtualDir, ByVal Dirpath As String, ByVal AltPath As String, ByVal filename As String, ByVal saveDrive As String) As Boolean
        Dim Datafile As Byte()
        Dim objFstream As FileStream = Nothing
        Dim savePath As String
        Try

            Dim fileserver As New Object

            Datafile = Nothing
            If Not Datafile Is Nothing Then
                savePath = saveDrive & "\" & Dirpath & AltPath
                If Directory.Exists(savePath) = False Then
                    Directory.CreateDirectory(savePath)
                End If
                objFstream = File.Open(savePath & "\" & filename, FileMode.Create, FileAccess.Write)

                Dim lngLen As Long = Datafile.Length
                objFstream.Write(Datafile, 0, CInt(lngLen))
                objFstream.Flush()
                objFstream.Close()

                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
            Return False
        Finally
            objFstream = Nothing
        End Try
    End Function

    ' Variables.

    Public Shared Function StreamFileForUpload(ByVal filename As String) As Byte()
        Dim fs As New FileStream(filename, FileMode.Open, FileAccess.Read)
        Dim ImageData As Byte() = New Byte(fs.Length - 1) {}
        fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length))
        fs.Close()
        Return ImageData
    End Function

    Public Shared Function CreateVDFolder(ByVal username As String, ByVal password As String, ByVal VD As String) As Boolean
        Dim Request As System.Net.HttpWebRequest
        Dim Response As System.Net.HttpWebResponse
        Dim MyCredentialCache As System.Net.CredentialCache

        Dim strDomain As String

        Dim strFolderURI As String

        Try
            ' Initialize variables.
            'strUserName = "UserName"
            'strPassword = "!Password"
            strDomain = "GV"
            strFolderURI = VD

            ' Create a new CredentialCache object and fill it with the network
            ' credentials required to access the server.
            MyCredentialCache = New System.Net.CredentialCache
            MyCredentialCache.Add(New System.Uri(strFolderURI), _
                "Basic", _
                New System.Net.NetworkCredential(username, password, strDomain) _
                )

            ' Create the HttpWebRequest object.
            Request = CType(System.Net.WebRequest.Create(strFolderURI),  _
                            System.Net.HttpWebRequest)

            ' Add the network credentials to the request.
            Request.Credentials = MyCredentialCache

            ' Specify the method.http://msdn.microsoft.com/en-us/library/aa142923(EXCHG.65).aspx
            Request.Method = "MKCOL"

            ' Send the MKCOL method request and get the
            ' response from the server.
            Response = CType(Request.GetResponse(), System.Net.HttpWebResponse)

            'Console.WriteLine("Folder created at " + strFolderURI + ".")

            ' Clean up.
            Response.Close()
            Return True
        Catch ex As Exception

            ' Catch any exceptions. Any error codes from the
            ' MKCOL method requests on the server will be caught
            ' here, also.
            Return False
            'Console.WriteLine(ex.Message)

        End Try

    End Function


    Public Shared Function GetLetterSetName(ByVal LetterSetId As Integer) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try
            Return db.GetSQLScalar("Select SLS_Name from I_SL_StockLetterSets where SLS_ID = " & LetterSetId)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Private Shared Function Gblfunc_Get_SLSLevels(ByVal SLSID As Long) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            Dim Level As Integer
            Dim L As Integer = 1
            Dim List As String = "(" & SLSID & ")"
            Dim Check As String = " X" & SLSID & "X "

            Dim myData As DataTable
            Dim myRow As DataRow

            Do
                Level += 1

                SQL = New StringBuilder

                With SQL
                    .Append("Select SLSLink_ChildSLSID From I_SL_SLSLink")
                    .Append(" Where SLSLink_SLSID in " & List)
                    .Append(" AND SLSLink_ChildSLSID <> 0")
                End With

                myData = db.GetSQLDataTable(SQL.ToString)

                If Not IsNothing(myData) Then
                    If myData.Rows.Count > 0 Then
                        L += 1
                        List = String.Empty

                        For Each myRow In myData.Rows
                            If List = String.Empty Then
                                List = "("
                            Else
                                List = List & ", "
                            End If

                            List = List & myRow.Item("SLSLink_ChildSLSID")

                            If InStr("X" & myRow.Item("SLSLink_ChildSLSID") & "X", Check) > 0 Then
                                'Possible circular reference! Crash out
                                Exit Function
                            End If
                            Check = Check & " X" & myRow.Item("SLSLink_ChildSLSID") & "X "


                        Next
                        List = List & ")"
                    End If
                End If

            Loop While L > Level And L < 50

            myData.Dispose()

            Return Level

        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function



    Public Shared Function Gblfunc_Get_SLSSQL(ByVal SLSID As Long, ByVal ClientType As Integer, ByVal Stage As Integer, ByVal ReturnType As Integer) As DataTable

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQLSelectId As New StringBuilder
        Dim SQLSelectLP As New StringBuilder
        Dim SQLSelectSLSId As New StringBuilder
        Dim SQLOrder As New StringBuilder
        Dim SQLSelectOrder As New StringBuilder
        Dim SQLWhere As New StringBuilder
        Dim SQLFrom As New StringBuilder
        Dim SQLSelect As New StringBuilder
        Dim SQLInner As New StringBuilder
        Dim SQL As New StringBuilder
        Try
            'ReturnType 1 = simple return, i.e user display
            'ReturnType 2 = Detailed return, i.e. admin system
            'ReturnType 3 = check return, i.e. when adding new child SLS

            'Stage 1 means return all (admin)
            'Stage 2 means only pre-sale (admin)
            'Stage 3 means only post-sale (admin)
            'All others are actual case stage numbers and should be used as is (user)


            Dim Levels As Integer
            Dim L, StartIdx, EndIdx As Integer


            'work out how many levels deep the Nesting needs to be
            Levels = Gblfunc_Get_SLSLevels(SLSID)
            L = 1

            Select Case Stage

                Case 1
                    'bring back everything (Admin)
                    StartIdx = 0
                    EndIdx = 0

                Case 2
                    'Pre sale only (admin)
                    StartIdx = 99
                    EndIdx = 0

                Case 3
                    'Post Sale Only
                    StartIdx = 0
                    EndIdx = 100

                Case 4
                    'After Closed Only
                    StartIdx = 0
                    EndIdx = 4321

                Case Else
                    StartIdx = Stage
                    EndIdx = Stage

            End Select

            'Build the Inner Query
            'This will retun a list of SLS Link ID's, along with The second level SLS ID and however many Order By Columns are needed.

            SQLSelectId.Append(" CASE")
            SQLSelectLP.Append(", CASE")
            SQLSelectSLSId.Append(", CASE")


            'Build Where Clause
            SQLWhere.Append(" Where I_SL_SLSLink_1.SLSLink_SLSID = " & SLSID)


            Do
                'SLSLinkID Case statement
                SQLSelectId.Append(" WHEN I_SL_SLSLink_" & L & ".SLSLink_LetterPackID <> 0 THEN I_SL_SLSLink_" & L & ".SLSLink_ID ")

                'LetterPack ID Case statement
                SQLSelectLP.Append(" WHEN I_SL_SLSLink_" & L & ".SLSLink_LetterPackID <> 0 THEN I_SL_SLSLink_" & L & ".SLSLink_LetterPackID ")

                'True SLS ID  Case Statement
                SQLSelectSLSId.Append(" WHEN I_SL_SLSLink_" & L & ".SLSLink_LetterPackID <> 0 THEN I_SL_SLSLink_" & L & ".SLSLink_SLSID ")

                'columns for order
                SQLSelectOrder.Append(", I_SL_SLSLink_" & L & ".SLSLink_Order as Order_" & L)

                'From Clause
                If L = 1 Then
                    SQLFrom.Append(" FROM I_SL_SLSLink I_SL_SLSLink_1")
                Else
                    SQLFrom.Append(" LEFT OUTER JOIN")
                    SQLFrom.Append(" I_SL_SLSLink I_SL_SLSLink_" & L)
                    SQLFrom.Append(" ON I_SL_SLSLink_" & L - 1 & ".SLSLink_ChildSLSID ")
                    SQLFrom.Append(" = I_SL_SLSLink_" & L & ".SLSLink_SLSID")
                End If

                'Where Clause (has to be at all levels) but for levels other than the first must allow nulls as well

                If ClientType <> 0 Then
                    SQLWhere.Append(" AND ((I_SL_SLSLink_" & L & ".SLSLink_CustomerType In (0," & ClientType & "))")

                    If L > 1 Then
                        SQLWhere.Append(" OR (I_SL_SLSLink_" & L & ".SLSLink_CustomerType is null))")
                    Else
                        SQLWhere.Append(")")
                    End If
                End If

                If StartIdx <> 0 Then
                    SQLWhere.Append(" AND ((I_SL_SLSLink_" & L & ".SLSLink_StageStart = 0 OR I_SL_SLSLink_" & L & ".SLSLink_StageStart <= " & StartIdx & ")")

                    If L > 1 Then
                        SQLWhere.Append(" OR (I_SL_SLSLink_" & L & ".SLSLink_StageStart is null))")
                    Else
                        SQLWhere.Append(")")
                    End If

                End If

                If EndIdx <> 0 Then
                    If EndIdx = 4321 Then
                        SQLWhere.Append(" AND ((I_SL_SLSLink_" & L & ".SLSLink_StageEnd = 0)")

                        If L > 1 Then
                            SQLWhere.Append(" OR (I_SL_SLSLink_" & L & ".SLSLink_StageEnd is null))")
                        Else
                            SQLWhere.Append(")")
                        End If

                    Else
                        SQLWhere.Append(" AND ((I_SL_SLSLink_" & L & ".SLSLink_StageEnd = 0 OR I_SL_SLSLink_" & L & ".SLSLink_StageEnd > " & EndIdx & ")")

                        If L > 1 Then
                            SQLWhere.Append(" OR (I_SL_SLSLink_" & L & ".SLSLink_StageEnd is null))")
                        Else
                            SQLWhere.Append(")")
                        End If

                    End If
                End If


                'Order by clause to be used in the outer Query
                If SQLOrder.ToString = String.Empty Then
                    SQLOrder.Append(" ORDER BY")
                Else
                    SQLOrder.Append(", ")
                End If

                SQLOrder.Append(" Order_" & L)

                L += 1

            Loop While L <= Levels And L < 50

            SQLSelectId.Append(" Else 0 end as Letter_SLSLinkID")
            SQLSelectLP.Append(" Else 0 End as LetterPackID")
            SQLSelectSLSId.Append(" Else 0 End as TrueSLSID")

            With SQLSelect
                .Append("SELECT " & SQLSelectId.ToString & SQLSelectLP.ToString & SQLSelectSLSId.ToString & SQLSelectOrder.ToString)

                .Append(", CASE WHEN I_SL_SLSLink_1.SLSLink_LetterpackID <> 0 THEN I_SL_SLSLink_1.SLSLink_SLSID ELSE I_SL_SLSLink_1.SLSLink_ChildSLSID END AS LP_SLSID")
                .Append(", I_SL_SLSLink_1.SLSLink_SLSID AS MainSLSID")
                .Append(", I_SL_SLSLink_1.SLSLink_ID as SLSLinkID")
                .Append(", I_SL_SLSLink_1.SLSLink_CustomerType as CustType")
                .Append(", I_SL_SLSLink_1.SLSLink_StageStart as Stage_Start")
                .Append(", I_SL_SLSLink_1.SLSLink_StageEnd as Stage_End")
            End With

            SQLInner.Append(SQLSelect.ToString & SQLFrom.ToString & SQLWhere.ToString)

            'Now build the Outer Query

            Select Case ReturnType

                Case 1
                    'Simple Return
                    With SQL
                        .Append("Select LetterPack_ID, LetterPack_Name ")

                        .Append(" FROM ")
                        .Append("(" & SQLInner.ToString & ") as CombinedSLSLinks ")
                        .Append(" Left Outer Join D_LetterPacks on CombinedSLSLinks.LetterPackID = D_LetterPacks.Letterpack_ID")

                        .Append(" Where Letterpack_ID is not null ")

                        .Append(SQLOrder.ToString)
                    End With


                Case 2
                    'Admin Return
                    With SQL
                        .Append("Select SLSLinkID, LetterPack_ID, LetterPack_Name, ")
                        .Append(" Case LP_SLSID When " & SLSID & " Then '' else SLS_Name end as SLSName,")
                        .Append(" Case CustType When 0 then 'All' When 1 then 'Private' When 2 then 'Corporate' End as CType,")
                        .Append(" Case Stage_Start when 0 then 'New Lead' when 100 then 'Initiated' else L_CaseStages_Start.CaseStage_Title End as CastStage_Title_Start, ")
                        .Append(" Case Stage_End when 99 then 'Sold' when 999 then 'Closed' when 0 then 'After Closed' else L_CaseStages_End.CaseStage_Title End as CastStage_Title_End, ")
                        .Append(" Order_1 as OrderCol")

                        .Append(" FROM ")
                        .Append("(" & SQLInner.ToString & ") as CombinedSLSLinks ")
                        .Append(" Left Outer Join D_LetterPacks on CombinedSLSLinks.LetterPackID = D_LetterPacks.Letterpack_ID")
                        .Append(" Left Outer Join L_CaseStages L_CaseStages_Start On CombinedSLSLinks.Stage_Start = L_CaseStages_Start.CaseStage_Number")
                        .Append(" Left Outer Join L_CaseStages L_CaseStages_End On CombinedSLSLinks.Stage_End = L_CaseStages_End.CaseStage_Number")
                        .Append(" Left Outer Join I_SL_StockLetterSets on CombinedSLSLinks.LP_SLSID = I_SL_StockLetterSets.SLS_ID")

                        .Append(" Where Letterpack_ID is not null ")

                        .Append(SQLOrder.ToString)
                    End With

                Case 3
                    'Admin check return
                    With SQL
                        .Append("Select TrueSLSID")
                        .Append(" FROM ")
                        .Append("(" & SQLInner.ToString & ") as CombinedSLSLinks ")
                    End With


            End Select
            Return db.GetSQLDataTable(SQL.ToString)

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQLSelectId = Nothing
            SQLSelectLP = Nothing
            SQLSelectSLSId = Nothing
            SQLOrder = Nothing
            SQLSelectOrder = Nothing
            SQLWhere = Nothing
            SQLFrom = Nothing
            SQLSelect = Nothing
            SQLInner = Nothing
            SQL = Nothing
        End Try

    End Function


    Public Shared Function Gblfunc_Get_BookmarkData(ByVal Bookmark As String, ByVal DataID As Long, ByVal DataType As Integer) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try


            Dim IntBookmark As Integer, IntDataLevel As Integer

            Dim LngCaseID As Long, LngContactID As Long, LngCandidateID As Long, LngCompanyID As Long, LngIDToFind As Long
            Dim IntCon As Integer, IntSaleCon As Integer, IntCWCon As Integer, intCCCON As Integer

            ' Dim SQL As New StringBuilder
            Dim myCase As Cases.CaseInfo

            Select Case DataType

                Case 1
                    'Case ID
                    myCase = Cases.GetCaseInfo(DataID)

                    If myCase.CaseId = 0 Then
                        Return String.Empty
                    End If


                    'Used
                    IntCon = GlobalCRM.CurrentConID
                    LngCaseID = DataID
                    LngContactID = myCase.Contactid
                    LngCandidateID = myCase.CandidateId
                    IntSaleCon = myCase.SalesConid
                    IntCWCon = myCase.CaseWorkerId
                    LngCompanyID = myCase.CompanyId
                    intCCCON = myCase.CaseCoordinatorID


                Case 2
                    'Person ID

                    Dim myPerson As PrivateIndividual.PersonInfo = PrivateIndividual.GetPerson(DataID)

                    If myPerson.PersonId = 0 Then
                        Return String.Empty
                    End If

                    'Used
                    IntCon = GlobalCRM.CurrentConID
                    LngContactID = DataID
                    LngCandidateID = DataID
                    LngCompanyID = myPerson.CompanyId

                    'Sub-levels (addresses, phone numbers and email) will be populated as needed when datalevel arrises

                    'Not used
                    LngCaseID = 0
                    IntSaleCon = 0
                    IntCWCon = 0


                Case 3
                    'CompanyID
                    Dim myCompany As Company.CompanyInfo = Company.GetCompany(DataID)

                    If myCompany.CompanyID = 0 Then
                        Return String.Empty
                    End If

                    'Used
                    IntCon = GlobalCRM.CurrentConID
                    LngCompanyID = DataID

                    'Sub-levels (addresses, phone numbers and email) will be populated as needed when datalevel arrises

                    'Not used
                    LngCaseID = 0
                    IntSaleCon = 0
                    IntCWCon = 0
                    LngContactID = 0
                    LngCandidateID = 0

            End Select

            'Get Bookmark info
            Dim myBookmark As Letters.BookmarkInfo = Letters.GetBookmark(Bookmark)


            If IsNothing(myBookmark) Then
                Return String.Empty
            End If


            'IntBookmark = Rst.Fields("Bookmark_ID")
            'IntBMDataType = Rst.Fields("Bookmark_DataType")
            'IntDataLevel = Rst.Fields("DLevel")
            'StrSelect = Rst.Fields("Bookmark_SQL")
            'StrColumn = Rst.Fields("Bookmark_SQLName")



            If myBookmark.Bookmark_SQLName = String.Empty Then
                myBookmark.Bookmark_SQLName = myBookmark.Bookmark_SQL
            End If

            Dim myDataSource As Letters.DataSourceInfo = Letters.GetLetterDataSource(myBookmark.BookMark_DataLevelID)

            If IsNothing(myDataSource) Then
                Return String.Empty
            End If


            'StrFrom = Rst.Fields("datasource_tabledef")
            'StrWhere = Rst.Fields("datasource_idcolumn")
            'IntSourceType = Rst.Fields("DataSource_Type")


            Select Case myBookmark.BookMark_DataLevelID
                Case 1
                    'case
                    LngIDToFind = LngCaseID

                Case 3
                    'product
                    LngIDToFind = myCase.ProductTypeId   'Cases.GetCaseInfo(LngCaseID)

                Case 4
                    'candidate
                    LngIDToFind = LngCandidateID

                    'No values for 4-9

                Case 5
                    'document
                    LngIDToFind = 0


                    'Case 1
                    '    'no longer in use (Lead)
                    '    LngIDToFind = 0

                    'Case 2
                    '    'Case
                    '    LngIDToFind = LngCaseID

                    'Case 3
                    '    'Advert
                    '    LngIDToFind = 0

                    '    'No values for 4-9

                    'Case 10
                    '    'Company
                    '    LngIDToFind = LngCompanyID

                    'Case 11
                    '    'Candidate
                    '    LngIDToFind = LngCandidateID

                    'Case 12
                    '    'Contact

                    '    'MJG 01/06/06 Auto email now sent to all clients not just corporate, if now corporate contact, then make it the client
                    '    If LngContactID > 0 Then
                    '        LngIDToFind = LngContactID
                    '    Else
                    '        LngIDToFind = LngCandidateID
                    '    End If

                    'Case 13
                    '    'Logged in consultant
                    '    LngIDToFind = GlobalCRM.CurrentConID

                    'Case 14
                    '    'Sales consultant
                    '    LngIDToFind = IntSaleCon

                    'Case 15
                    '    'Casework consultant
                    '    LngIDToFind = IntCWCon

                    'Case 16
                    '    'AdvertLink
                    '    'Not in use

                    'Case 17
                    '    'Company Main Address
                    '    If LngCompanyID <> 0 Then
                    '        'Get postal address

                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngCompanyID, PrivateIndividual.AddressLinkTypes.Correspondence, PrivateIndividual.AddressTypes.Company)

                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 18
                    '    'Company Inv Address
                    '    If LngCompanyID <> 0 Then
                    '        'Get postal address
                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngCompanyID, PrivateIndividual.AddressLinkTypes.Invoice, PrivateIndividual.AddressTypes.Company)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 19
                    '    'Contact Main Address
                    '    If LngContactID <> 0 Then
                    '        'Get postal address
                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngContactID, PrivateIndividual.AddressLinkTypes.Correspondence, PrivateIndividual.AddressTypes.Person)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 20
                    '    'Contact Inv address
                    '    If LngContactID <> 0 Then
                    '        'Get postal address
                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngContactID, PrivateIndividual.AddressLinkTypes.Invoice, PrivateIndividual.AddressTypes.Person)

                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 21
                    '    'Candidate Main Address
                    '    If LngCandidateID <> 0 Then
                    '        'Get postal address
                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngCandidateID, PrivateIndividual.AddressLinkTypes.Correspondence, PrivateIndividual.AddressTypes.Person)

                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 22
                    '    'Candidate Inv Address
                    '    If LngCandidateID <> 0 Then
                    '        'Get postal address
                    '        LngIDToFind = PrivateIndividual.GetPostalAddressLink(LngCandidateID, PrivateIndividual.AddressLinkTypes.Invoice, PrivateIndividual.AddressTypes.Person)

                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 23
                    '    'Contact phone
                    '    If LngContactID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetPhoneLink(LngContactID, PrivateIndividual.PhoneTypes.PhoneMain, PrivateIndividual.AddressTypes.Person)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 24
                    '    'Contact Fax
                    '    If LngContactID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetPhoneLink(LngContactID, PrivateIndividual.PhoneTypes.PhoneFax, PrivateIndividual.AddressTypes.Person)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 25
                    '    'Candidate phone
                    '    If LngCandidateID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetPhoneLink(LngCandidateID, PrivateIndividual.PhoneTypes.PhoneMain, PrivateIndividual.AddressTypes.Person)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 26
                    '    'Candidate Fax
                    '    If LngCandidateID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetPhoneLink(LngCandidateID, PrivateIndividual.PhoneTypes.PhoneFax, PrivateIndividual.AddressTypes.Person)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 27
                    '    'Contact EMail
                    '    If LngContactID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetEmailLink(LngContactID)
                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    'Case 28
                    '    'Candidate Email
                    '    If LngCandidateID <> 0 Then
                    '        'Get phone number
                    '        LngIDToFind = PrivateIndividual.GetEmailLink(LngCandidateID)

                    '    Else
                    '        LngIDToFind = 0
                    '    End If

                    '    'No 29

                    'Case 30
                    '    'Case Financials
                    '    LngIDToFind = LngCaseID

                    'Case 31
                    '    'Case Calculator Points
                    '    LngIDToFind = LngCaseID

                    'Case 32
                    '    'Candidates Partner. Need to go via the dependants table to the Person table

                    '    LngIDToFind = PrivateIndividual.GetCandidatePartnerLink(LngCaseID)

                    'Case 33
                    '    'Documents required, different process bellow.

                    'Case 34
                    '    'GV Data, different process bellow

                    'Case 35 'bookmark INfo data
                    '    LngIDToFind = LngCaseID

                    'Case 36 ' Bookmark Dependant cost
                    '    LngIDToFind = LngCaseID

                    'Case 37, 38
                    '    LngIDToFind = LngCaseID

                    'Case Else
                    '    LngIDToFind = 0

            End Select

            'Get the data and output the bookmark

            Select Case myDataSource.Type 'IntSourceType

                Case 1
                    'Traditional data based bookmark
                    'Create data query using table definitions above
                    Dim SQL As New StringBuilder
                    Dim myData As DataTable

                    SQL.Append("Select " & myBookmark.Bookmark_SQL & " From " & myDataSource.TableDef & " Where " & myDataSource.IDColumn & " = " & LngIDToFind)

                    myData = db.GetSQLDataTable(SQL.ToString)

                    If myData.Rows.Count > 0 Then

                        If myData.Rows(0).Item(myBookmark.Bookmark_SQLName).ToString <> "" Then

                            Select Case myBookmark.Bookmark_DataType 'IntBMDataType

                                Case 2
                                    'Currency, make sure to preserve 2 decimal places

                                    Gblfunc_Get_BookmarkData = CStr(FormatNumber(myData.Rows(0).Item(myBookmark.Bookmark_SQLName), 2))


                                Case Else '(and 1 the default)

                                    'Treat as plain text, no processing

                                    Gblfunc_Get_BookmarkData = myData.Rows(0).Item(myBookmark.Bookmark_SQLName)

                            End Select
                        End If


                    End If
                    myData.Dispose()

                Case 2
                    'Function based Bookmark. Depending on the bookmark, use a DB func to process

                    Select Case IntDataLevel

                        Case 33
                            'Doc Req bookmark

                            If DataType = 1 Then
                                'Only process for cases, not valid for people or companies

                                Select Case IntBookmark

                                    Case 216
                                        'List outstanding
                                        Gblfunc_Get_BookmarkData = Gblfunc_Get_DocReqList(DataID)

                                    Case 217
                                        'List received
                                        Gblfunc_Get_BookmarkData = Gblfunc_Get_DocReqList(DataID, 2)

                                    Case 218
                                        'All reqs
                                        Gblfunc_Get_BookmarkData = Gblfunc_Get_DocReqList(DataID, 3)

                                End Select
                            End If
                    End Select


                Case 3

                    'GV Data. Use Bookmark to extract generic GV data
                    Return db.GetSQLScalar("Select GVBookmarks_Data from L_GVBookmarks where GVBookmarks_BookmarkID = " & IntBookmark)

            End Select
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try


    End Function



    Public Shared Function GblFunc_Create_DocStore(ByVal Description As String, ByVal StoreType As String, ByVal Case_ID As Long, Optional ByVal LinkType As Integer = 1) As Long
        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        'Dim DbServerName1 As String
        Try


            Dim DocStoreId As Long
            Dim StrPath As String, StrYear As String, StrMonth As String
            Dim SQL As New StringBuilder

            With SQL
                .Append("INSERT INTO D_DocStore (DocStore_EnteredBy, DocStore_DTEntered, DocStore_CaseID, DocStore_Description, DocStore_Type, DocStore_LinkType)")
                .Append(" VALUES('" & cmn.SwapQuotes(GlobalCRM.CurrentConName) & "','" & cmn.FormatDate(Now, True) & "'," & Case_ID & ",'" & cmn.SwapQuotes(Description) & "','" & cmn.SwapQuotes(StoreType) & "', " & LinkType & ")")
            End With
            DocStoreId = db.ExecuteSQL(SQL.ToString, True)


            StrYear = Year(Now)
            StrMonth = Month(Now)

            'Fso = CreateObject("Scripting.FileSystemObject")


            Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader

            Dim DbServerName As String = CType(configurationAppSettings.GetValue("DocStoreServer", GetType(System.String)), String)


            'DbServerName1 = CommonDataFunctions.GetCustomizationValue("VirtualDocDir")
            ' xxx check if live, otherwise use test docstore directory (to avoid clash of docstore id numbers)
            If InStr(CommonFuncs.GetDBConnectionString.ToString.ToLower, "global") Then
                ' StrPath = "\\" & DbServerName.Trim & "\DocStore$\" & StrYear
                StrPath = DbServerName & "\DocStore\" & StrYear
            Else
                'StrPath = "\\" & DbServerName.Trim & "\DocStore$\Test\" & StrYear
                StrPath = DbServerName & "\DocStore\Test\" & StrYear
            End If


            'If Not Directory.Exists(StrPath) Then
            '    Directory.CreateDirectory(StrPath)
            'End If
            'If Not Fso.folderExists(StrPath) Then MkDir(StrPath)


            'CreateVDFolder(GlobalCRM.CurrentNTUserName, GlobalCRM.CurrentNTPassword, StrPath.Replace("\\" & DbServerName & "\", DbServerName1))

            StrPath = StrPath & "\" & StrMonth

            'CreateVDFolder(GlobalCRM.CurrentNTUserName, GlobalCRM.CurrentNTPassword, StrPath.Replace("\\" & DbServerName & "\", DbServerName1) & "\" & StrMonth)

            'If Not Directory.Exists(StrPath) Then
            '    Directory.CreateDirectory(StrPath)
            'End If

            StrPath = StrPath & "\" & DocStoreId


            'Directory.CreateDirectory(StrPath)

            SQL = New StringBuilder

            With SQL
                .Append("Update D_Docstore set DocStore_filePath = '" & cmn.SwapQuotes(StrPath) & "'")
                .Append(" Where Docstore_ID = " & DocStoreId)
            End With

            If db.ExecuteSQL(SQL.ToString) Then
                Return DocStoreId
            End If

        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            cmn = Nothing
        End Try
    End Function


    Public Shared Function Gblfunc_Get_DocStorePath(ByVal DocStoreID As Long) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try
            'Dim DbServerName As String
            Dim DocPath As String


            Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader

            Dim DbServerName1 As String = CType(configurationAppSettings.GetValue("DocStoreServer", GetType(System.String)), String)

            ' DbServerName = CommonDataFunctions.GetCustomizationValue("VirtualDocDir")
            DocPath = db.GetSQLScalar("Select Docstore_FilePath from D_Docstore where Docstore_ID = " & DocStoreID)

            If DocPath.Trim = String.Empty Then

                If InStr(CommonFuncs.GetDBConnectionString.ToString.ToLower, "global") Then
                    'DocPath = "\\" & DbServerName1.Trim & "\DocStore$\DB\" & DocStoreID
                    DocPath = DbServerName1.Trim & "\DocStore\DB\" & DocStoreID
                Else
                    'DocPath = "\\" & DbServerName1.Trim & "\DocStore$\Test\DB\" & DocStoreID
                    DocPath = DbServerName1.Trim & "\DocStore\Test\DB\" & DocStoreID
                End If
            End If
            'Return DocPath.Replace("\\" & DbServerName1 & "\", DbServerName)
            Return DocPath
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function


    Public Shared Function GblFunc_Add_Doc(ByVal Filename As String, ByVal Name As String, ByVal DocStore As Long) As Long
        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("INSERT INTO D_Docs (Doc_DocStoreID, Doc_Filename, Doc_Name)")
                .Append(" VALUES(" & DocStore & ", '" & cmn.SwapQuotes(Filename) & "','" & cmn.SwapQuotes(Name) & "')")
            End With
            Return db.ExecuteSQL(SQL.ToString, True)

        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetBookmark(ByVal Bookmark As String, Optional ByVal SysVersion As Boolean = True) As BookmarkInfo


        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            Dim myData As DataTable
            SQL = New StringBuilder
            With SQL
                .Append("SELECT Bookmark_ID, Bookmark_SQL, Bookmark_SQLName, BookMark_DataLevelID as DLevel, Bookmark_DataType, bookmark_legacy")
                .Append(" FROM I_SL_Bookmarks")
                .Append(" WHERE Bookmark_Text = '" & cmn.SwapQuotes(Bookmark) & "'")
                If SysVersion Then
                    .Append(" AND Bookmark_VersionID <= " & GlobalCRM.SysVersion)
                End If
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    Dim myBookmark As New BookmarkInfo

                    With myBookmark
                        .Bookmark_ID = myData.Rows(0).Item("bookmark_id")
                        .Bookmark_SQL = IIf(IsDBNull(myData.Rows(0).Item("bookmark_sql")), "", myData.Rows(0).Item("bookmark_sql"))
                        .Bookmark_SQLName = IIf(IsDBNull(myData.Rows(0).Item("bookmark_sqlname")), "", myData.Rows(0).Item("bookmark_sqlname"))
                        .BookMark_DataLevelID = myData.Rows(0).Item("dlevel")
                        .Bookmark_DataType = myData.Rows(0).Item("bookmark_datatype")
                        .Bookmark_Legacy = myData.Rows(0).Item("bookmark_legacy")
                    End With

                    Return myBookmark
                Else
                    Return Nothing
                End If
            Else
                Return Nothing

            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function BookmarkList() As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT Bookmark_ID, Bookmark_Text, Bookmark_Desc, Bookmark_Legacy")
                .Append(" FROM I_SL_Bookmarks")
                .Append(" WHERE (Bookmark_Legacy = 0)")
                .Append(" ORDER BY Bookmark_Text")
            End With

            Return db.GetSQLDataTable(SQL.ToString)

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetLetterDataSource(ByVal DataLevelId As Integer) As DataSourceInfo
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable

            With SQL
                .Append(" SELECT I_SL_DataSource.DataSource_TableDef, I_SL_DataSource.DataSource_IDColumn, I_SL_DataSource.DataSource_Type")
                .Append(" FROM I_SL_DataLevels INNER JOIN I_SL_DataSource ON I_SL_DataLevels.DataLevel_SourceID = I_SL_DataSource.DataSource_ID")
                .Append(" WHERE I_SL_DataLevels.DataLevel_ID = " & DataLevelId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myInfo As New DataSourceInfo

                    With myInfo
                        .TableDef = IIf(IsDBNull(myData.Rows(0).Item("datasource_tabledef")), "", myData.Rows(0).Item("datasource_tabledef"))
                        .IDColumn = IIf(IsDBNull(myData.Rows(0).Item("datasource_idcolumn")), "", myData.Rows(0).Item("datasource_idcolumn"))
                        .Type = myData.Rows(0).Item("datasource_type")
                    End With

                    Return myInfo
                Else
                    Return Nothing

                End If
            Else : Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function Gblfunc_Get_DocReqList(ByVal CaseID As Long, Optional ByVal ListType As Integer = 1) As String


        Dim StrList As String
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myDocs As DataTable

            With SQL
                .Append("SELECT DocReq_Name FROM D_DocReqs")
                .Append(" WHERE (DocReq_CaseID = " & CaseID & ")")


                Select Case ListType

                    Case 3
                        'All doc Reqs

                        'Nothing need be added

                    Case 2
                        'Received Docs only

                        .Append(" AND Not (DocReq_DTReceived Is Null)  ")

                    Case Else '(and 1 which is the default)
                        'Outstanding Docs

                        .Append(" AND (DocReq_DTReceived Is Null)  ")
                End Select

                .Append(" ORDER BY DocReq_Name")

            End With

            myDocs = db.GetSQLDataTable(SQL.ToString)

            If myDocs.Rows.Count = 0 Then
                'No reqs, done

                Select Case ListType

                    Case 3

                        StrList = "No document requirements on case"

                    Case 2
                        StrList = "No received  document requirements"

                    Case Else '(and 1)

                        StrList = "No outstanding document requirements"

                End Select
            Else
                'cursor through reqs adding to list
                Dim myRow As DataRow

                StrList = String.Empty

                For Each myRow In myDocs.Rows
                    If Not IsDBNull(myRow.Item("DocReq_Name")) Then
                        'Only add to list if there is something in the field

                        'only do a CRLF for second and subsequent lines
                        'do before value not after so no trailing CRLF at the end
                        If StrList <> "" Then StrList = StrList & vbCrLf

                        'Add the data
                        StrList = StrList & myRow.Item("DocReq_Name")
                    End If

                Next
            End If

            myDocs.Dispose()

            Gblfunc_Get_DocReqList = StrList
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GblFunc_Add_FileToStore(ByVal Filename As String, ByVal Name As String, ByVal DocStore As Long) As Long


        'New version of Add doc function that moves the filename generation away from the calling function and into this func.
        'This ties in with the new Docstore filepath system.
        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            Dim StrFilename As String, StrDir As String, IntNo As Integer
            If DocStore = 0 Then
                Exit Function
            End If
            StrDir = Gblfunc_Get_DocStorePath(DocStore)

            If Not File.Exists(Filename) Then
                Exit Function
            End If
            'copy to docstore
            StrFilename = StrDir & "\" & Path.GetFileName(Filename)

            If File.Exists(StrFilename) Then
                'Filename already in DocStore, try another

                IntNo = 0

                Do
                    IntNo = IntNo + 1
                Loop While File.Exists(StrFilename & "_" & IntNo) And IntNo < 100

                If IntNo = 100 Then Exit Function

                StrFilename = Left(StrFilename, Len(StrFilename) - 4) & "_" & IntNo & "_" & Right(StrFilename, 4)
            End If



            Directory.CreateDirectory(StrDir)

            FileCopy(Filename, StrFilename)

            'If UploadFileWebservice(DocStore, Path.GetFileName(Filename), Filename) = True Then ', True, GlobalCRM.CurrentNTUserName, GlobalCRM.CurrentNTPassword) 
            With SQL
                .Append("INSERT INTO D_Docs (Doc_DocStoreID, Doc_Filename, Doc_Name)")
                .Append(" VALUES(" & DocStore & ", '" & cmn.SwapQuotes(StrFilename) & "','" & cmn.SwapQuotes(Name) & "')")
            End With

            Return db.ExecuteSQL(SQL.ToString, True)
            'Else : Return 0
            'End If
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "Letters.gblFunc_Add_FileToStore")
            Return 0
        Finally
            db = Nothing
            SQL = Nothing
            cmn = Nothing
        End Try


    End Function

    Public Shared Function Gblfunc_Process_Bookmarks(ByVal TextIn As String, ByVal LinkID As Long, ByVal LinkType As Integer) As String

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim StrMail As String
            Dim objDR As DataRow
            Dim objDT As DataTable = Nothing
            Dim StrBM As String, StrValue As String
            Dim IntPos As Integer, IntNo As Integer, IntCurrent As Integer, IntScan As Integer

            Dim ArrBM(0 To 9) As String

            If TextIn.Trim = String.Empty Then
                Return String.Empty
            End If

            StrMail = TextIn

            'Find out how many bookmarks are in the text


            Do
                IntPos = InStr(IntPos + 1, StrMail, "<")

                If IntPos > 0 Then IntNo = IntNo + 1

            Loop While IntPos <> 0

            If IntNo = 0 Then
                'No bookmarks in template, exit out with raw text
                Return StrMail
                Exit Function
            End If

            'We have at least one bookmark to process

            'Create Array to store Bookmarks
            Dim ArrStrBM() As String
            ReDim ArrStrBM(0 To IntNo)

            'Scan through the text checking and adding the bookmarks

            IntPos = 0
            IntCurrent = 1

            Do
                IntPos = InStr(IntPos + 1, StrMail, "<")

                If IntPos > 0 Then
                    StrBM = Right(StrMail, Len(StrMail) - IntPos + 1)
                    If InStr(StrBM, ">") > 0 Then
                        StrBM = Left(StrBM, InStr(StrBM, ">"))

                        'We have a complete bookmark, check and see if we already have this one

                        IntScan = 1
                        Do
                            If ArrStrBM(IntScan) = StrBM Then StrBM = String.Empty
                            IntScan = IntScan + 1
                        Loop While IntScan <= IntCurrent And IntScan < 100

                        If StrBM <> "" Then
                            ArrStrBM(IntCurrent) = StrBM
                            IntCurrent = IntCurrent + 1
                        End If
                    End If
                End If


            Loop While IntPos <> 0

            IntCurrent = IntCurrent - 1

            If IntCurrent > 0 Then

                Do
                    'set var to bookmark
                    StrValue = ArrStrBM(IntCurrent)

                    'Trim off the [ and ] to poll for data
                    StrValue = Left(StrValue, Len(StrValue) - 1)
                    StrValue = Right(StrValue, Len(StrValue) - 1)

                    'Get the actual data

                    Select Case StrValue

                        Case "DOCREQLISTALL", "DOCREQLISTOUTSTANDING", "DOCREQLISTRECEIVED"
                            'objDT = Documents.GetDocsRequiredForCase(GlobalCRM.CurrentCaseID)
                            StrValue = String.Empty
                            If objDT Is Nothing Then
                            Else
                                If objDT.Rows.Count > 0 Then
                                    For Each objDR In objDT.Rows
                                        Select Case StrValue.ToUpper
                                            Case "DOCREQLISTOUTSTANDING"
                                                If IIf(objDR("rec") Is DBNull.Value, "", objDR("rec")) = String.Empty Then
                                                    StrValue &= objDR("DocReq_Name") & vbCrLf
                                                End If
                                            Case "DOCREQLISTRECEIVED"
                                                If IIf(objDR("rec") Is DBNull.Value, "", objDR("rec")) <> "" Then
                                                    StrValue &= objDR("DocReq_Name") & vbCrLf
                                                End If
                                            Case Else
                                                StrValue &= objDR("DocReq_Name") & vbCrLf
                                        End Select
                                    Next
                                End If
                            End If


                        Case "CC_ADDRESS"
                            StrValue = Gblfunc_Get_BookmarkData(StrValue, LinkID, LinkType)
                            Dim Parts() As String = StrValue.Split("- ")
                            Dim intCtr As Integer
                            StrValue = String.Empty
                            For intCtr = 0 To UBound(Parts, 1)
                                If Parts(intCtr).Trim <> "" Then
                                    StrValue &= Parts(intCtr) & vbCrLf
                                End If
                            Next
                        Case Else
                            StrValue = Gblfunc_Get_BookmarkData(StrValue, LinkID, LinkType)
                    End Select
                    'If we got something, write it into the template over any instances of the bookmark
                    If StrValue <> "" Then
                        StrMail = Replace(StrMail, ArrStrBM(IntCurrent), StrValue)
                    End If

                    IntCurrent = IntCurrent - 1

                Loop While IntCurrent > 0

            End If

            Return StrMail
        Catch ex As Exception
            'CommonFunc.ErrorHandler(ex, "Letters.gblFunc_Add_FileToStore")
            Return 0
        Finally
            SQL = Nothing
        End Try


    End Function


    Public Shared Function SaveLetter(ByVal Letter As LetterInfo, ByVal EnteredBy As String) As Long

        Dim cmn As New CommonFuncs
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            If Letter.LetterId = 0 Then
                With SQL
                    .Append("Insert Into D_Letters (Letter_Name,Letter_FilePath, Letter_CustomerType, Letter_OutputType, Letter_Type, Letter_EnteredBy, Letter_FileType) ")
                    .Append(" Values('" & cmn.SwapQuotes(Letter.LetterName) & "',")
                    .Append(" '" & cmn.SwapQuotes(Letter.Filepath) & "',")
                    .Append(" " & Letter.CustomerType & ", ")
                    .Append(" " & Letter.OutputType & ", ")
                    .Append(" " & Letter.LetterType & ", ")
                    .Append("'" & cmn.SwapQuotes(EnteredBy) & "',")
                    .Append(Letter.Filetype & ")")
                End With

                Return db.ExecuteSQL(SQL.ToString, True)

            ElseIf Letter.LetterId > 0 Then
                With SQL
                    .Append("Update D_Letters set ")
                    .Append(" Letter_Name = '" & cmn.SwapQuotes(Letter.LetterName) & "',")
                    .Append(" Letter_Filepath = '" & cmn.SwapQuotes(Letter.Filepath) & "',")
                    .Append(" Letter_CustomerType = " & Letter.CustomerType & ", ")
                    .Append(" Letter_OutputType = " & Letter.OutputType & ", ")
                    .Append(" Letter_Type = " & Letter.LetterType)
                    .Append(" WHERE Letter_ID = " & Letter.LetterId)
                End With

                If db.ExecuteSQL(SQL.ToString) Then
                    Return Letter.LetterId
                End If

            End If
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function LetterPacks(Optional ByVal LetterTypeId As Integer = 0) As DataTable
        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_LetterPacks.LetterPack_ID, D_LetterPacks.LetterPack_Name, I_SL_LetterTypes.LetterType_Name ")
                .Append(" FROM D_LetterPacks INNER JOIN I_SL_LetterTypes ON D_LetterPacks.LetterPack_LetterTypeID = I_SL_LetterTypes.LetterType_ID ")
                If LetterTypeId > 0 Then
                    .Append(" where letterpack_lettertypeid = " & LetterTypeId)
                End If
                .Append(" Order By letterpack_id") 'Letterpack_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try

    End Function

    Public Shared Function SLAAvailableList(ByVal LetterPackId As Integer, ByVal SLSTypeId As Integer) As DataTable

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            'Populate not used in list
            With SQL
                .Append("SELECT I_SL_StockLetterSets.SLS_ID, I_SL_StockLetterSets.SLS_Name, SLSType_Name")
                .Append(" FROM I_SL_StockLetterSets LEFT OUTER JOIN")
                .Append("  (SELECT     SLSLink_SLSID")
                .Append(" FROM I_SL_SLSLink")
                .Append(" WHERE      SLSLink_LetterpackID = " & LetterPackId & ") SLSInUse ON I_SL_StockLetterSets.SLS_ID = SLSInUse.SLSLink_SLSID")
                .Append(" Left Outer Join I_SL_SLSTypes on I_SL_StockLetterSets.SLS_TypeID = I_SL_SLSTypes.SLSType_ID")
                .Append(" WHERE (SLSInUse.SLSLink_SLSID Is Null)")
                If SLSTypeId > 0 Then
                    .Append(" AND SLS_TypeID = " & SLSTypeId)
                End If
                .Append(" Order by SLSType_Name, SLS_Name")

                'Call GPL(strSQL, "Letterpacks_SLSAvailableList", Form_FMA_LetterPacks.List_SLSAvailable)
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try

    End Function

    Public Shared Function SLSList(ByVal LetterPackId As Integer) As DataTable

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("SELECT I_SL_SLSLink.SLSLink_SLSID, I_SL_StockLetterSets.SLS_Name, SLSType_Name, I_SL_StockLetterSets.SLS_TypeID, I_SL_SLSLink.SLSLink_ID")
                .Append(" FROM I_SL_SLSLink INNER JOIN")
                .Append(" I_SL_StockLetterSets ON I_SL_SLSLink.SLSLink_SLSID = I_SL_StockLetterSets.SLS_ID")
                .Append(" Left Outer Join I_SL_SLSTypes on I_SL_StockLetterSets.SLS_TypeID = I_SL_SLSTypes.SLSType_ID")
                .Append(" WHERE (I_SL_SLSLink.SLSLink_LetterPackID = " & LetterPackId & ")")
                .Append(" Order by SLSType_Name, SLS_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try


    End Function

    Public Shared Function SLSTypeList() As DataTable
        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("Select SLSType_ID, SLSType_Name from I_SL_SLSTypes Order By SLSType_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function LettersInPack(ByVal LetterPackId As Integer) As DataTable

        'strSQL = "Select SLSType_ID, SLSType_Name from I_SL_SLSTypes Order By SLSType_Name"
        'Call GPL(strSQL, "Letterpacks_SLSType", Form_FMA_LetterPacks.Combo_AvailableSLS)
        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL

                .Append("SELECT D_LetterLink.LetterLink_ID, D_Letters.Letter_ID, D_Letters.Letter_Name,")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 1) AND D_LetterLink.LetterLink_OutputType IN (0, 1, 4, 5) THEN 'Yes' ELSE '' END AS PrivatePrint, ")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 1) AND D_LetterLink.LetterLink_OutputType IN (0, 2, 4, 6) THEN 'Yes' ELSE '' END AS PrivateEmail,")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 1) AND D_LetterLink.LetterLink_OutputType IN (0, 3, 5, 6) THEN 'Yes' ELSE '' END AS PrivateFile, ")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 2) AND D_LetterLink.LetterLink_OutputType IN (0, 1,4, 5) THEN 'Yes' ELSE '' END AS CoPrint, ")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 2) AND D_LetterLink.LetterLink_OutputType IN (0,2, 4, 6) THEN 'Yes' ELSE '' END AS CoEmail, ")
                .Append(" CASE WHEN D_LetterLink.LetterLink_CustomerType IN (0, 2) AND D_LetterLink.LetterLink_OutputType IN (0, 3, 5, 6) THEN 'Yes' ELSE '' END AS CoFile, D_LetterLink.LetterLink_Copies")
                .Append(" FROM D_LetterLink INNER JOIN D_Letters ON D_LetterLink.LetterLink_LetterID = D_Letters.Letter_ID")
                .Append(" WHERE (D_LetterLink.LetterLink_LetterPackID = " & LetterPackId & ")")
                .Append(" ORDER BY D_LetterLink.LetterLink_PrintOrder")
            End With

            Return db.GetSQLDataTable(SQL.ToString)

            'Call GblFunc_Populate_List(strSQL, "LettersInPack_List", Form_FMA_LetterPacks.List_Letters)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function LetterTypeList() As DataTable
        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT LetterType_ID, LetterType_Name FROM i_sL_LetterTypes")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function


    Public Shared Function LetterList(ByVal LetterType As Integer) As DataTable

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Select Letter_ID, Letter_Name from D_Letters where Letter_Type = " & LetterType)
                .Append(" Order By Letter_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Private Shared Function LetterPrintOrder(ByVal LetterPackId As Integer) As Integer
        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim c As Integer

            c = db.GetSQLScalar("Select isnull(Max(LetterLink_PrintOrder),0) as MaxOrder from D_LetterLink where Letterlink_Letterpackid = " & LetterPackId)
            c += 1

            Return c
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function


    Public Shared Function AddLetterLink(ByVal LetterInfo As LetterInfo, ByVal LetterPackId As Integer, ByVal ConName As String, ByVal LetterPackName As String) As Long

        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Dim tsk As New TasksEvents
        Dim Msg As StringBuilder

        Try
            SQL = New StringBuilder
            Dim LinkId As Long

            With SQL
                .Append("Insert into D_LetterLink")
                .Append("(LetterLink_LetterID, LetterLink_LetterPackID,")
                .Append(" LetterLink_CustomerType, LetterLink_OutputType, LetterLink_Copies, LetterLink_PrintOrder,")
                .Append(" LetterLink_EnteredBy, LetterLink_DTEntered)")

                .Append(" VALUES")
                .Append(" (" & LetterInfo.LetterId & ", " & LetterPackId & ", ")
                .Append(LetterInfo.CustomerType & ", " & LetterInfo.OutputType & ", 1, " & LetterPrintOrder(LetterPackId) & ", ")
                .Append("'" & cmn.SwapQuotes(ConName) & "','" & cmn.FormatDate(Now, True) & "')")
            End With
            LinkId = db.ExecuteSQL(SQL.ToString, True)
            If LinkId > 0 Then


                Msg = New StringBuilder
                With Msg

                    .Append("Letter number " & LetterInfo.LetterId & " (" & LetterInfo.LetterName & ") added to Letterpack number " & LetterPackId & " (" & LetterPackName & ") with the following output values.")
                    .Append(vbCrLf & vbCrLf)
                    .Append("Client Type: ")
                    Select Case LetterInfo.CustomerType

                        Case 0
                            .Append("All")

                        Case 1
                            .Append("Private")
                        Case 2
                            .Append("Corporate")

                    End Select

                    .Append(vbCrLf & "Output Type: ")

                    Select Case LetterInfo.OutputType

                        Case 0
                            .Append("All")

                        Case 1
                            .Append("Print")

                        Case 2
                            .Append("Email")

                        Case 3
                            .Append("File")

                        Case 4
                            .Append("Print + Email")

                        Case 5
                            .Append("Print + File")

                        Case 6
                            .Append("Email + File")

                    End Select

                    .Append(vbCrLf & "No. Copies :1")
                End With

                tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

            End If

            Return LinkId
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            SQL = Nothing
            cmn = Nothing
            tsk = Nothing
            Msg = Nothing
        End Try
    End Function

    Public Shared Function UpdatePrintOrder(ByVal LinkId As Long, ByVal LetterPackId As Integer, ByVal PrintOrder As Integer, ByVal UpDown As Integer) As Boolean
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("Update D_Letterlink set Letterlink_printorder = " & PrintOrder)
                .Append(" Where letterlink_letterpackID = " & LetterPackId)
                .Append(" And letterlink_printorder = " & PrintOrder + UpDown)
            End With

            If db.ExecuteSQL(SQL.ToString) Then

                SQL = New StringBuilder

                With SQL
                    .Append("Update D_Letterlink set Letterlink_printorder = " & PrintOrder + UpDown)
                    .Append(" Where letterlink_ID = " & LinkId)
                End With

                Return db.ExecuteSQL(SQL.ToString)

            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function RemoveSLSLink(ByVal SLSLinkID As Long) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Dim tsk As New TasksEvents
        Try

            Dim mySLSLink As Letters.SLSLinkInfo = Letters.GetSLSLink(SLSLinkID)
            Dim mySLS As Letters.SLSInfo
            Dim Msg As New StringBuilder

            'Build sys event message
            If mySLSLink.ChildSLSId > 0 Then
                'Removing an SLS link
                mySLS = Letters.GetStockLetterSet(mySLSLink.ChildSLSId)

                If mySLS.SLSId = 0 Then

                    Msg.Append("Unknown SLS number " & mySLSLink.ChildSLSId & vbCrLf)
                Else
                    Msg.Append("SLS number " & mySLSLink.ChildSLSId & " (" & mySLS.SLSName & ")" & vbCrLf)

                End If
            Else
                'Removing a Letterpack link
                Dim myPack As LetterPackInfo = Letters.GetLetterPack(mySLSLink.LetterpackId)


                If myPack.LetterPackId = 0 Then

                    Msg.Append("Unknown Letterpack number " & mySLSLink.LetterpackId & vbCrLf)
                Else
                    Msg.Append("Letterpack number " & mySLSLink.LetterpackId & " (" & myPack.LetterPackName & ")" & vbCrLf)
                End If
            End If

            mySLS = Letters.GetStockLetterSet(mySLSLink.SLSId)

            If mySLS.SLSId = 0 Then

                Msg.Append("removed from unknown SLS number " & mySLSLink.SLSId)
            Else
                Msg.Append("removed from SLS number " & mySLS.SLSId & " (" & mySLS.SLSName & ")")
            End If

            'Message ready, delete record

            If Not db.ExecuteSQL("Delete From I_SL_SLSLink where SLSLink_ID = " & SLSLinkID) Then
                Exit Function
            End If


            'Update remaining links order to keep consistent
            SQL = New StringBuilder
            With SQL
                .Append("Update I_SL_SLSLink Set SLSLink_Order = SLSLink_Order -1 ")
                .Append(" Where SLSLink_SLSID = " & mySLSLink.SLSId)
                .Append(" And SLSLink_Order > " & mySLSLink.PrintOrder)
            End With

            If Not db.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            'Save admin message and exit
            tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

            Return True
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function AddSLSLink(ByVal SLSID As Integer, ByVal LinkID As Integer, ByVal LinkType As Integer, ByVal ClientType As Integer, ByVal StageFrom As Integer, ByVal StageTo As Integer) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Dim tsk As New TasksEvents
        Try
            Dim obj As Object = db.GetSQLScalar("Select Max(SLSLink_Order) as MaxOrder from I_SL_SLSLInk where SLSLink_SLSID = " & SLSID)
            Dim MaxOrder As Integer

            Dim mySLSInfo As Letters.SLSInfo
            Dim myPackInfo As Letters.LetterPackInfo
            Dim Msg As New StringBuilder

            If IsDBNull(obj) Then
                MaxOrder = 0
            End If

            If MaxOrder = 0 Then
                MaxOrder = 1
            Else
                MaxOrder += 1
            End If

            'Put together admin message.
            If LinkType = 2 Then
                mySLSInfo = Letters.GetStockLetterSet(LinkID)
                Msg.Append("SLS number " & LinkID & " (" & mySLSInfo.SLSName & ")")
            Else
                myPackInfo = Letters.GetLetterPack(LinkID)
                Msg.Append("Letterpack number " & LinkID & " (" & myPackInfo.LetterPackName & ")")
            End If

            mySLSInfo = Letters.GetStockLetterSet(SLSID)

            Msg.Append(vbCrLf & " added to SLS number " & SLSID & " (" & mySLSInfo.SLSName & ")")
            SQL = New StringBuilder

            With SQL
                .Append("Insert Into I_SL_SLSLink")
                .Append(" (SLSLink_SLSID, SLSLink_LetterpackID, SLSLink_CustomerType, SLSLink_StageStart, SLSLink_StageEnd, SLSLink_Order, SLSLink_ChildSLSID)")
                .Append(" VALUES(")
                .Append(SLSID & ", ")

                If LinkType = 2 Then
                    .Append("0 , ")
                Else
                    .Append(LinkID & ", ")
                End If

                .Append(ClientType & ", ")
                .Append(StageFrom & ", ")
                .Append(StageTo & ", ")
                .Append(MaxOrder & ", ")

                If LinkType = 2 Then
                    .Append(LinkID & ")")
                Else
                    .Append("0 )")
                End If
            End With

            If Not db.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function OutputName(ByVal OutputId As Integer) As String
        Dim db As New CRM.DataLayer.DataAccess
        Try
            Return db.GetSQLScalar("Select Output_Name from L_OutputTypes where Output_ID = " & OutputId)
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function StockLetterList(ByVal ClientType As Integer, ByVal LetterType As Integer, ByVal OutputType As Integer) As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Try
            SQL = New StringBuilder
            Dim SQLWhere As New StringBuilder

            With SQL
                .Append("SELECT D_Letters.Letter_ID, MIN(D_Letters.Letter_Name) AS Name, MIN(isnull(I_SL_LetterTypes.LetterType_Name,'None')) AS LetterType,")
                .Append(" min(CASE D_Letters.Letter_CustomerType when 0 then 'Any' when 1 then 'Private' when 2 then 'Corporate' end) as ClientType,")
                .Append(" min(CASE D_Letters.Letter_OutputType when 0 then 'Any' when 1 then 'Print' when 2 then 'E-Mail' when 3 then 'File' end) as OutputType,")
                .Append(" COUNT(D_LetterLink.LetterLink_LetterID) As Uses")
                .Append(" FROM D_Letters LEFT OUTER JOIN")
                .Append(" D_LetterLink ON D_Letters.Letter_ID = D_LetterLink.LetterLink_LetterID LEFT OUTER JOIN")
                .Append(" I_SL_LetterTypes ON D_Letters.Letter_Type = I_SL_LetterTypes.LetterType_ID")

                If ClientType + LetterType + OutputType > 0 Then

                    .Append(" WHERE")

                    With SQLWhere


                        If LetterType > 0 Then
                            .Append(" D_Letters.Letter_Type = " & LetterType)
                        End If

                        Select Case ClientType


                            Case 1
                                'Show only 'All' and 'Private' letters

                                If .ToString <> "" Then .Append(" AND")

                                .Append(" D_Letters.Letter_CustomerType in (0,1)")

                            Case 2
                                'Show only 'All' and 'Corporate' letters

                                If .ToString <> "" Then .Append(" AND")

                                .Append(" D_Letters.Letter_CustomerType in (0,2)")

                        End Select

                        If OutputType > 0 Then

                            If .ToString <> "" Then .Append(" AND")

                            .Append(" D_Letters.Letter_OutputType in (0," & OutputType & ")")
                        End If

                        SQL.Append(.ToString)
                    End With
                End If


                .Append(" GROUP BY D_Letters.Letter_ID")
                ' .Append(" ORDER BY ")

                'Select Case OrderBy

                '    Case 1
                '        .Append("MIN(D_Letters.Letter_ID), MIN(D_Letters.Letter_Name)")

                '    Case 2
                '        .Append("MIN(D_Letters.Letter_Name)")

                '    Case 3
                '        strSQL = strSQL & "MIN(I_SL_LetterTypes.LetterType_Name), MIN(D_Letters.Letter_Name)"

                '    Case 4
                '        strSQL = strSQL & "MIN(CASE D_Letters.Letter_CustomerType when 0 then 'Any' when 1 then 'Private' when 2 then 'Corporate' end), MIN(D_Letters.Letter_Name)"

                '    Case 5
                '        strSQL = strSQL & "MIN(CASE D_Letters.Letter_OutputType when 0 then 'Any' when 1 then 'Print' when 2 then 'E-Mail' when 3 then 'File' end), MIN(D_Letters.Letter_Name)"

                '    Case 6
                '        strSQL = strSQL & "COUNT(D_LetterLink.LetterLink_LetterID) Desc, MIN(D_Letters.Letter_Name)"

                'End Select

            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function DeleteLetter(ByVal LetterId As Integer, ByVal EventMessage As String) As Boolean
        Dim db As New CRM.DataLayer.DataAccess
        Dim tsk As New TasksEvents
        Try
            If db.ExecuteSQL("Delete from D_Letters where Letter_ID = " & LetterId) Then
                tsk.GblFunc_Add_SysEvent("Admin (SL)", EventMessage)
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function

#Region "EmailTemplates"


    Public Structure TemplateInfo
        Public TemplateName As String
        Public SubjectLine As String
        Public Body As String
    End Structure


    Public Shared Function EmailTemplateList() As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT D_EmailTemplates.EMailTemplate_ID, MAX(D_EmailTemplates.EMailTemplate_Name) AS Title, ")
                .Append("COUNT(I_SL_SLSMailTemplates.SLSMailTemplate_ID) AS Uses FROM D_EmailTemplates LEFT OUTER JOIN I_SL_SLSMailTemplates ")
                .Append("ON D_EmailTemplates.EMailTemplate_ID = I_SL_SLSMailTemplates.SLSMailTemplate_TemplateID GROUP BY D_EmailTemplates.EMailTemplate_ID")
                .Append(" ORDER BY MAX(D_EmailTemplates.EMailTemplate_Name)")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    'Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As TemplateInfo
    '    Dim db As New CRM.DataLayer.DataAccess
    '    Dim SQL As StringBuilder
    '    Try

    '        SQL = New StringBuilder
    '        Dim myData As DataTable
    '        Dim myInfo As New TemplateInfo

    '        With SQL
    '            .Append("Select EMailTemplate_Name, EMailTemplate_SubjectLine, EMailTemplate_Body from D_EMailTemplates")
    '            .Append(" Where EMailTemplate_ID = " & TemplateId)
    '        End With

    '        myData = db.GetSQLDataTable(SQL.ToString)

    '        If Not IsNothing(myData) Then
    '            If myData.Rows.Count > 0 Then

    '                With myInfo
    '                    .TemplateName = myData.Rows(0).Item("emailtemplate_name")
    '                    .SubjectLine = myData.Rows(0).Item("emailtemplate_subjectline")
    '                    .Body = myData.Rows(0).Item("emailtemplate_body")
    '                End With

    '            End If

    '        End If

    '        myData.Dispose()

    '        Return myInfo
    '    Catch ex As Exception
    '        Return Nothing
    '    Finally
    '        db = Nothing
    '        SQL = Nothing
    '    End Try
    'End Function


#End Region

    Public Shared Function GblFunc_Send_MailEngine(ByVal AddressTo As String, ByVal AddressFrom As String, ByVal SubjectLine As String, ByVal Body As String, Optional ByVal AddressCC As String = "", Optional ByVal AddressBCC As String = "", Optional ByVal DocStoreID As Long = 0) As Integer
        Dim db As New CRM.DataLayer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Try
            Dim From As String = AddressFrom

            If From.Trim = String.Empty Then

                Dim myCon As CommonDataFunctions.ConsultantInfo

                myCon = CommonDataFunctions.GetConsultantInfo(GlobalCRM.CurrentConID)

                If myCon.ConId = 0 Then
                    MsgBox("Cannot find senders address, send cancelled", MsgBoxStyle.Exclamation, "Error")
                    tsk.GblFunc_Add_SysEvent("Mail Engine", "No send address provided, couldnt find user no. " & GlobalCRM.CurrentConID)
                    Exit Function
                Else
                    If myCon.Email.Trim = String.Empty Then
                        MsgBox("Cannot find senders address, send cancelled", MsgBoxStyle.Exclamation, "Error")
                        tsk.GblFunc_Add_SysEvent("Mail Engine", "No send address provided, no address for user no. " & GlobalCRM.CurrentConID)
                        Exit Function
                    Else
                        From = myCon.Email
                    End If
                End If

            End If

            If AddressTo.Trim = String.Empty Then
                MsgBox("No destination address provided, send cancelled", MsgBoxStyle.Exclamation, "Error")
                Exit Function
            End If

            Dim SQL As New StringBuilder

            With SQL
                .Append("Insert into S_SendEMail (SendEmail_CreatedBy, SendEmail_CreatedByID, SendEmail_From, ")
                .Append(" SendEMail_To,SendEMail_CC, SendEMail_BCC, SendEMail_Subject, SendEMail_Body, SendEmail_Attachments,sendemail_linktype,sendemail_linkid)")
                .Append(" VALUES(")
                .Append(" '" & GlobalCRM.CurrentConName & "',")
                .Append(" " & GlobalCRM.CurrentConID & ", ")
                .Append(" '" & cmn.SwapQuotes(From) & "',")
                .Append(" '" & cmn.SwapQuotes(AddressTo) & "',")
                .Append(" '" & cmn.SwapQuotes(AddressCC) & "',")
                .Append(" '" & cmn.SwapQuotes(AddressBCC) & "',")
                .Append(" '" & cmn.SwapQuotes(SubjectLine) & "',")
                .Append(" '" & cmn.SwapQuotes(Body) & "', ")
                .Append(DocStoreID & ",1," & GlobalCRM.CurrentCaseID & " )")
            End With

            If db.ExecuteSQL(SQL.ToString) Then
                'Return True
                Return 1
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try

    End Function
    Public Shared Function SendMailMessage(ByVal AddressTo As String, ByVal AddressFrom As Integer, ByVal caseid As Integer, ByVal SubjectLine As String, ByVal Body As String, ByVal candID As String, Optional ByVal AddressCC As String = "", Optional ByVal AddressBCC As String = "", Optional ByVal AttachmentPath As String = "") As Integer

        Dim db As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            If AddressTo.Trim = String.Empty Then
                MsgBox("No destination address provided, send cancelled", MsgBoxStyle.Exclamation, "Error")
                Exit Function
            End If
            'Attachments processing


            SQL = New StringBuilder

            With SQL
                .Append("Insert into s_mailboxmessage (Mailboxmsg_Fromid, Mailboxmsg_caseid, Mailboxmsg_ClientEmail, ")
                .Append(" Mailboxmsg_msg,Mailboxmsg_subject,Mailboxmsg_ClientID, Mailboxmsg_date,Mailboxmsg_Attachment)")
                .Append(" VALUES(")
                .Append(" " & GlobalCRM.CurrentConID & ", ")
                .Append(GlobalCRM.CurrentCaseID & ",")
                .Append(" '" & cmn.SwapQuotes(AddressTo) & "',")
                '.Append(" '" & SwapQuotes(AddressCC) & "',")
                '.Append(" '" & SwapQuotes(AddressBCC) & "',")
                .Append(" '" & cmn.SwapQuotes(Body) & "',")
                .Append(" '" & cmn.SwapQuotes(SubjectLine) & "',")
                .Append(candID & ",")
                .Append("getdate()" & ",'" & cmn.SwapQuotes(AttachmentPath) & "'" & " )")
            End With

            If db.ExecuteSQL(SQL.ToString) Then
                'Return True
                Return 1
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try

    End Function

    Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As EmailTemplate
        Dim db As New CRM.DataLayer.DataAccess
        Dim myData As DataTable
        Try

            GetEmailTemplate = Nothing

            myData = db.GetSQLDataTable("Select EmailTemplate_subjectline, EmailTemplate_Body from D_EmailTemplates where EmailTemplate_id = " & TemplateId)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myTemp As New EmailTemplate

                    myTemp.Subject = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_subjectline")), "", myData.Rows(0).Item("emailtemplate_subjectline"))
                    myTemp.Body = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_body")), "", myData.Rows(0).Item("emailtemplate_body"))

                    Return myTemp
                Else
                    Return Nothing
                End If
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            myData = Nothing
            db = Nothing
        End Try
    End Function
End Class
